第二课 基础篇_SQL语法
一.通用语法
1.SQL语句可以单行或多行书写,分号结尾.
2.SQL语句可以使用空格或缩进增强语句的可读性.
3.MySQL数据库库的SQL语句不区分大小写,关键字建议使用大写.
4.注释:
①单行注释:–注释内容或#注释内容(MySQL特有)
②多行注释:
/*注释内容*/
二.SQL分类
三.数据类型
1.数值类型
| 类型 | 大小 | 有符号(SIGNED)范围 | 无符号(UNSIGNED)范围 | 描述 |
|---|---|---|---|---|
| TINYINT | 1 byte | (-128,127) | (0,255) | 小整数 |
| SMALLINT | 2 bytes | (-32768,32767) | (0,65535) | 大整数 |
| MEDIUMINT | 3 bytes | (-8388608,8388607) | (0,16777215) | 大整数 |
| INT或INTEGER | 4 bytes | (-2147473648,2147473647) | (0,4294967295) | 大整数 |
| BIGINT | 8 bytes | (-263,263-1) | (0,2^64-1) | 极大整数 |
| FLOAT | 4 bytes | (-3.402823466 E+38,3.402823466351 E+38) | 0和(1.175494351 E-38,3.402823466 E+38) | 单精度浮点数 |
| DOUBLE | 8 bytes | (-1.7976931348623157 E+308,1.7976931348623157 E+308) | 0和(2.2250738585072014 E-308,1.7976931348623157 E+308) | 双精度浮点数 |
| DECIMAL | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 | 小数值(经确定点数) |
2.字符串类型
| 类型 | 大小 | 描述 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串 |
| VARCHAR | 0-65535 bytes | 变长字符串 |
| TINYBLOB | 0-255 bytes | 不超过255个字符的二进制数据 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-15777215 bytes | 二进制形式的中长文本数据 |
| MEDIUMTEXT | 0-15777215 bytes | 中长文本数据 |
| LONGBLOB | 0-4294967295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4294967295 bytes | 极大文本数据 |
3.日期时间类型
| 类型 | 大小 | 范围 | 格式 | 描述 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期 |
| TIME | 3 | -838:59:59 至 838:59:59 | HH:MM:SS | 时间或持续时间 |
| YEAR | 1 | 1901 至 2155 | YYYY | 年份 |
| DATETIME | 8 | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间 |
| TIMESTAMP | 4 | 1970-01-01 00:00:01 至 2038 01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间,时间戳 |
4.数值类型应用
设计员工表如下:
①编号(纯数字)
②员工工号(字符串类型,长度不超过10位)
③员工姓名(字符串类型,长度不超过10位)
④性别(男/女,存储一个汉字)
⑤年龄(不存在负数)
⑥身份证号(二代身份证均为18位,末位可能是X)
⑦入职时间(取年月日)
mysql> create table emp(-> id int comment '编号',-> workno varchar(10) comment '工号',-> name varchar(10) comment '姓名',-> gender char(1) comment '性别',-> age tinyint unsigned comment '年龄',-> idcard char(18) comment '身份证号',-> entrydate date comment '入职时间'-> )comment '员工表';
Query OK, 0 rows affected (0.11 sec)
四.DDL
1.查询数据库
SHOW DATABASE;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
2.创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 发字符集] [COLLATE 裴谞规则];
①直接创建
mysql> create database itcast;
Query OK, 1 row affected (0.03 sec)
②如果数据库存在则不进行创建
mysql> create database if not exists itcast;
Query OK, 1 row affected, 1 warning (0.01 sec)
③按默认字符集创建数据库
mysql> create database test001 default charset utf8mb4;
Query OK, 1 row affected (0.02 sec)
3.删除数据库
DROP DATABASE [IF EXISTS] 数据库名;
mysql> drop database test001;
Query OK, 0 rows affected (0.02 sec)
4.使用数据库
USE 数据库名;
①使用itcast数据库
mysql> use itcast
Database changed
②查询当前使用的数据库
SELECT DATABASE();
5.创建查询表
①创建表
CREAT TABLE 表名(
字段1 字段1类型[COMMENT 字段1注释],
字段n 字段n类型[COMMENT 字段n注释]
)[VCOMMENT 表注释];
mysql> create table tb_user(-> id int comment '编号',-> name varchar(50) comment '姓名',-> age int comment '年龄',-> gender varchar(1) comment '性别'-> )comment '用户表';
Query OK, 0 rows affected (0.14 sec)
②查询表
SHOW TABLES;
mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| tb_user |
+------------------+
1 row in set (0.00 sec)
③查询表结构
DESC 表名;
mysql> desc tb_user;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
| gender | varchar(1) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
④查询指定表的建表语句
SHOW CREAT TABLE 表名;
mysql> show create table tb_user;
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------+
| Table | Create Table|
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------+
| tb_user | CREATE TABLE `tb_user` (`id` int DEFAULT NULL COMMENT '编号',`name` varchar(50) DEFAULT NULL COMMENT '姓名',`age` int DEFAULT NULL COMMENT '年龄',`gender` varchar(1) DEFAULT NULL COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户
表' |
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------+
1 row in set (0.01 sec)
6.修改表
①添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
案例:
为emp表增加一个新字段"昵称"为nickname,类型为varchar(20).
mysql> alter table emp add nickname varchar(20) comment '昵称';
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc emp;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| workno | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| idcard | char(18) | YES | | NULL | |
| entrydate | date | YES | | NULL | |
| nickname | varchar(20) | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
8 rows in set (0.01 sec)
②修改字段
修改字段类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)[COMMENT 注释] [约束];
案例:
将emp表的nickname字段修改为username,类型为varchar(30).
mysql> alter table emp change nickname username varchar(30) comment '用户名';
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc emp;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| workno | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| idcard | char(18) | YES | | NULL | |
| entrydate | date | YES | | NULL | |
| username | varchar(30) | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
③删除字段
ALTER TABLE 表名 DROP 字段名;
案例:
将emp表的字段username删除.
mysql> alter table emp drop username;
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
④修改表名
ALTER TABLE 表名 RENAME TO 新表名;
案例:
将emp表的表名修改为employee.
mysql> alter table emp rename to employee;
Query OK, 0 rows affected (0.04 sec)
⑤删除表
删除表
DROP TABLE [IF EXISTS] 表名;
mysql> drop table if exists tb_user;
Query OK, 0 rows affected (0.13 sec)
删除制定表,并重新创建该表
TRUNCATE TABEL 表名;
五. MySQL图形化界面安装
1.常用图形化界面
sqlyog Navicat DataGrip
2.下载安装DataGrip
3.数据源及配置
创建mysql-base项目→添加Data Source→my sql
4.创建数据库
右键点击MySQL-@locallhost→新建schema→新建数据库:test
5.创建表
右键点击数据库:test→新建:Table
表名:user→注释:用户表→添加字段Columns:id int 编号→添加字段Columns:name varchar(50) 姓名→Execute
6.修改表
右键点击表:user→Modify Table
添加新字段age int 年龄
7.控制台模式
右键点击数据库test→New→Query Console
show databases;→点击执行
六.DML
Data manipulation Language(数据操作语言),用来对数据库中标的数据记录进行增删改操作.
1.添加数据(INSTER)
①给指定字段添加数据
INSERT INTO 表名 (字段1,字段2,…) VALUES (值1,值2,…);
②给全部字段添加数据
INSERT INT O表名 VALUES (值1,值2,…);
③批量添加数据
INSERT INTO 表名 (字段1,字段2,…) VALUES (值1,值2,…),(值1,值2,…),(值1,值2,…);
INSERT INT O表名 VALUES (值1,值2,…),(值1,值2,…),(值1,值2,…);
④注意:
插入数据时,制定的字段顺序与值得顺序一致.
字符串和日期型数据应包含在引号中.
插入的数据大小,应该在字段的规定范围内.
⑤实例
在数据库itcast右键→New→Query Console
insert into employee(id, workno, name, gender, age, idcard, entrydate) values (1,'1','Itcast','男',10,'123456789012345678','2000-01-01');
select * from employee;
insert into employee values (1,'1','张无忌','男',10,'123456789012345678','2000-01-01');
select * from employee;
insert into employee values (3,'3','赵敏','女',18,'123456789012345671','2010-03-05'),(4,'4','小昭','女',17,'123456789012345672','2010-09-01');
2.修改数据(UPDATE)
UPDATE 表名 SET 字段名1=值1,字段名2=值2,…[WHERE 条件];
注意:如果没有条件则修改整张表的数据.
--修改id为1的数据,将name的值修改为'张三丰'
update employee set name='张三丰' where id =1;
--修改id为3的数据,将name修改为'玄冥',gender修改为'男'
update employee set name ='玄冥',gender='男' where id=3;
--修改全部记录的entrydate为'2020-01-09'
update employee set entrydate='2020-01-09';
3.删除数据(DELETE)
DELETE FROM 表名 [WHERE 条件];
注意:没有条件会删除整张表的所有数据.
不能删除某一个字段的值(可以使用UPDAE).
--删除gender条件为'女'的数据记录
delete from employee where gender='女';
--删除所有数据记录
delete from employee;
七.DQL
Data Query Language(数据库查询语言),查询数据库中标的记录.
书写顺序:
SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数
1.基本查询
①多字段查询
SELECT 字段1,字段2,字段3… FROM 表名;
SELECT * FROM 表名;
--查询数据
--数据准备
create table emp(id int comment '编号',workno varchar(10) comment '工号',name varchar(10) comment '姓名',gender char(1) comment '性别',age tinyint unsigned comment '年龄',idcard char(18) comment '身份证号',workaddress varchar(50) comment '工作地址',entrydate date comment '入职时间'
) comment '员工表';--数据记录
insert into emp (id,workno,name,gender,age,idcard,workaddress,entrydate)
values (1,'1','柳岩','女',20,'123456789012345678','北京','2000-01-01'),(2,'2','张无忌','男',18,'123456789012344478','深圳','2005-09-01'),(3,'3','韦一笑','男',38,'123456789012345578','北京','2000-08-01'),(4,'4','赵敏','女',18,'123456789012345666','杭州','2009-12-01'),(5,'5','小昭','女',16,'123456789012345678','杭州','2007-07-01'),(6,'6','杨肖','男',28,'12345678901234567x','北京','2000-01-01'),(7,'7','范瑶','男',40,'123456789012345678','天津','2000-01-01'),(8,'8','爱丽丝','女',38,'123456789012345678','北京','2000-01-01'),(9,'9','段誉','男',19,'123456789012345558','天津','2000-01-01'),(10,'10','虚竹','男',21,'123456789066345678','南京','2000-01-01'),(11,'11','周伯通','男',48,'123456777012345678','上海','2000-01-01'),(12,'12','王重阳','男',52,'123459989012345678','江苏','2000-01-01'),(13,'13','灭绝','女',49,'123459989012345678','西安','2000-01-01'),(14,'14','周芷诺','女',18,NULL,'西安','2000-01-01'),(15,'15','阿朱','女',17,'123456789012345645','北京','2000-01-01'),(16,'16','胡青牛','男',65,'12345678901234566x','杭州','2000-01-01');
--1.查询指定字段name,workno,age返回值
select name,workno,age from emp;
--2.查询所有字段返回值
select id,workno,name,gender,age,idcard,workaddress,entrydate from emp;
select * from emp;
--3.查询所有员工的工作地址,起别名
select workaddress as '工作地址' from emp;
select workaddress '工作地址' from emp;
--4.查询工作地址并去除重复值
select distinct workaddress '工作地址' from emp;
②.设置别名
SELECT 字段1 [AS 别名1],字段2 [AS 别名2] … FROM 表名;
③.去除重复记录
SELECT DISINCT 字段列表 FROM 表名;
2.条件查询
①语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
②条件
| 比较运算符 | 功能 |
|---|---|
| > | 大于 |
| >= | 大于等于 |
| < | 小鱼 |
| <= | 小于等于 |
| = | 等于 |
| <> 或 != | 不等于 |
| BETWEEN … AND … | 在某个范围内(包含最小,最大值) |
| IN(…) | 在IN之后的列表中的值,多选一 |
| LIKE 占位符 | 模糊匹配(_匹配单个字符,%匹配任意个字符) |
| IS NULL | 是NULL |
| 逻辑运算符 | 功能 |
|---|---|
| AND 或 && | 并且(多个条件同时成立) |
| OR 或 || | 或者(多个条件任意一个成立) |
| NOT 或 ! | 非,不是 |
③实例
--条件查询
--1.查询年龄等于65的员工
select * from emp where age=65;
--2.查询年龄小于20的员工
select * from emp where age<20;
--3.查询年龄小于等于20的员工
select * from emp where age<=20;
--4.查询没有身份证号的员工信息
select * from emp where idcard is null;
--5.查询有身份证号的员工信息
select * from emp where idcard is not null;
--6.查询年龄不等于65的员工信息
select * from emp where age!=65;
select * from emp where age<>65;
--7.查询年龄在15(包含15)到20(包含20)之间的员工
select * from emp where age between 15 and 20;
select * from emp where age>=15 and age<=20;
select * from emp where age>=15 && age<=20;
--8.查询性别为女且年龄小于25的员工
select * from emp where gender='女' && age<25;
--9.查询年龄等于18或20或40的员工
select * from emp where age=18 or age=20 or age=40;
select * from emp where age=18 || age=20 || age=40;
select * from emp where age in(18,20,40);
--10.查询姓名为两个字的员工
select * from emp where name like '__';
--11.查询身份证号最后一位是X的员工
select * from emp where idcard like '%x';
3.聚合函数
①介绍
将一列数据作为一个整体进行纵向计算.
②常见聚合函数
| 函数 | 功能 |
|---|---|
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
③语法
SELECT 聚合函数(字段列表) FROM 表名;
注意:所有聚合函数不计算NULL值.
④实例
--聚合函数
--1.统计企业员工数量
select count(*) from emp;
select count(idcard) from emp;
--2.统计企业员工平均年龄
select avg(age) from emp;
--3.统计员工的最大年龄
select max(age) from emp;
--4.统计员工的最小年龄
select min(age) from emp;
--5.统计杭州地区员工年龄之和
select sum(age) from emp where workaddress='杭州';
4.分组查询
①语法
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
②where与having区别
执行时机不同:where是分组之前进行过滤,不满足where条件的不参与分组;having是分组后对结果进行过滤.
执行条件不同:where不能对聚合函数进行判断;having可以.
③实例
--分组查询
--1.根据性别分组,统计男性员工和女性员工的数量
select gender,count(*) from emp group by gender;
--2.根据性别分组,统计男性员工和女性员工的平均年龄
select gender,avg(age) from emp group by gender;
--3.查询年龄小于45的员工,并根据工作地址分组,获取员工数量大于等于3的工作地址
select workaddress,count(*) from emp where age <45 group by workaddress having count(*)>=3;
5.排序查询
①语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;
②排序方式
ASC:升序(默认)
DESC:降序
注意:多字段排序,当第一个字段值相同时才会根据第二个字段进行排序.
③实例
--排序查询
--1.根据年龄对公司的员工进行升序排序
select * from emp order by age asc;
--2.根据入职时间,对员工进行降序排序
select * from emp order by entrydate desc;
--3.根据年龄对公司的员工进行升序排序,年龄相同,再按照入职时间进行降序排序
select * from emp order by age asc,entrydate desc;
6.分页查询
①语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
注意:
起始索引从0开始,起始索引=(查询页码-1)*每页显示记录数.
分页查询是数据库方言,不同的数据库有不同的实现,MySQL中是LIMIT.
如果查询的是第一页的数据,起始索引可以省略,直接简写为limit 10.
②实例
--分页查询
--1.查询第1页员工数据,每页显示6条记录
select * from emp limit 0,6;
select * from emp limit 6;
--2.查询第3页员工数据,每页显示6条记录--起始索引=(页码-1)*每页显示记录数
select * from emp limit 12,6;
7.DQL执行顺序
八.DCL
Data Control Language(数据库控制语言),用来管理数据库用户,控制数据库访问权限.
注意:主机名可以使用通配符.
1.用户管理
①查询用户
USE mysql;
SELECT * FROM user;
用户信息在系统数据库mysql的user表中.
②创建用户
CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’
③修改用户密码
ALTER USER ‘用户名’@‘主机名’ IDENTIFIED WITH mysql_native_password BY ‘新密码’;
④删除用户
DROP USER ‘用户名’@‘主机名’;
⑤实例
--创建rexmen用户,只能够在当前主机localhost访问,密码123456
create user 'rexmen'@'localhost' identified by '123456';
--创建nana用户,可以在任意主机访问数据库,密码123456;
create user 'nana'@'%' identified by '123456';
--修改用户nana的访问密码为1234;
alter user 'nana'@'%' identified with mysql_native_password by '1234';
--删除rexmen@localhost用户
drop user 'rexmen'@'localhost';
2.权限控制
常用权限
| 权限 | 说明 |
|---|---|
| ALL,ALL PRIVILEGES | 所有权限 |
| SELECT | 查询数据 |
| INSERT | 插入数据 |
| UPDATE | 修改数据 |
| DELETE | 删除数据 |
| ALTER | 修改表 |
| DROP | 删除数据库/表/视图 |
| CREATE | 创建数据库/表 |
①查询权限
SHOW GRANTS FOR ‘用户名’@‘主机名’;
②授予权限
GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@‘主机名’;
③撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@‘主机名’;
④实例
--查询权限
show grants for 'nana'@'%';
--授予权限
grant all on itcast.* to 'nana'@'%';
--撤销权限
revoke all on itcast.* from 'nana'@'%';
注意:
多个权限之间使用逗号分隔.
授权时数据库名和表名可以使用*进行通配代表所有.
支付宝扫一扫
微信扫一扫
.png)
.png)
.png)
