SQL语句分类：
1.DDL DATA define LANGUAGE 数据定义语句： CREATE ALTER ;
2.DML DATA manipulation LANGUAGE 数据操作语句 增删改语句 INSERT / DELETE /UPDATE ;
3.DQL DATA QUERY LANGUAGE 数据查询语句 SELECT ;
一、增删改
1.增加 INSERT
方式1：
语法：INSERT INTO 表名 (字段1,字段2。。。) VALUES(值1，值2。。。);
mysql> INSERT INTO emp (empno,ename,deptno) VALUES(1,'tom',1010);
方式2：插入部分字段的值，字段的个数和顺序要与值的个数和顺序一致
mysql> INSERT INTO emp (empno,ename) VALUES(2,'tom');
方式3：省略字段，但是值的个数和顺序要和表里的一致；
mysql> INSERT INTO emp VALUES (3,'bob',1002);
2.删除 DELETE
方式1：删除表中的所有数据；
语法：DELETE FROM 表名；
mysql> DELETE FROM emp;
方式2：删除符合条件的数据；
语法： DELETE FROM 表名 WHERE 条件;
mysql> DELETE FROM emp WHERE ename ='tom';
操作符：
=等于
<小于
<=小于等于
>大于
>=大于等于
<>不等于 !=
BETWEEN 下限值 AND 上线值
注意：下限值和上线值是包括的。
如：mysql> DELETE FROM emp WHERE empno BETWEEN 2 AND 4; 包含2和4；
逻辑表达式：
连接我们的条件表达式的
AND 并且 所有的条件都要满足的情况下才会去匹配。
mysql> DELETE FROM emp WHERE empno>=2 AND empno<=4;
OR 或者 满足其中之一的条件都能匹配。
mysql> DELETE FROM emp WHERE empno=1 OR empno =5;
3.更新数据UPDATE
不带条件可以更改整个字段的值：
mysql> UPDATE emp SET deptno =1001;
带条件，更改符合条件的数据：
mysql> UPDATE emp SET deptno =1002 WHERE ename='tom';
更改多个字段的值
mysql> UPDATE emp SET empno=5,deptno=1003 WHERE ename ='tom';
/*二、简单查询DQL (data query language)
1.查询所有列的所有数据；
使用*的效率相对较低，
select * from 表名；
select sid,sname ,birthday from 表名；
2.查询指定列的数据；
select 字段1，字段2.。。。from 表名；
3.去重查询
SELECT DISTINCT sname FROM student;
4.列计算：数值列可以进行加减乘除运算
SELECT sid,sname ,sal+comm FROM student；
5.字段的别名 ，as可以省略,别名的引号可以省略
SELECT sid AS 编号,sname AS 姓名,birthday AS 生日 FROM student;
6.条件查询
SELECT * FROM student WHERE birthday >'2000-02-01'AND sex='女' OR sid=1 ;
in关键词：
字段的值符合括号里的任何一个就满足条件
SELECT * FROM student WHERE sid IN(1,4,9);
SELECT * FROM student WHERE sname IN('tom','bob') ;
not in: 不是括号里的任何一个就满足条件
is null ：值为null;
is not null:值不为null;
7.模糊查询 like
% 代表任意多个字符
_ 代表一个字符
select * from student where sname like '张';
select * from student where sname like '___张';
8.排序 order by
asc ：ascend 升序，默认为升序；
desc : descend 降序；
select * from student order by sal asc;
按多个字段排序：
语法： select * from 表名 order by 字段1 desc ,字段2 desc;
select * from student order by sex desc ,sal desc;
9.limit 限制返回的条数 一般用于数据分页
limit m,n; m代表开始的索引，n代表个数；
SELECT * FROM emp LIMIT 15,5;
limit n; n代表个数，索引从0开始；
10. null和任何值计算都得null;
函数：ifnull(字段，值）； 若该字段中有值为null;
SELECT ename ,sal+IFNULL(comm,0) FROM emp;
函数：concat(字段，值或字段）；可以把字段的值拼接起来；
SELECT ename ,CONCAT(sal,'元') sal FROM emp;
*/
1.创建学生表student：学生编号sid 主键自增长，姓名sname 非空，性别sex 非空，生日birthday ，年龄age ,班级编号cid ;
mysql> create table student(sid int primary key auto_increment,sname varchar(10)
not null,sex varchar(10) not null,birthday date,age int,cid int);
Query OK, 0 rows affected (0.03 sec)
这是学生表student：
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(10) | NO | | | |
| sex | varchar(10) | NO | | | |
| birthday | date | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| cid | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
2.创建班级表class: 班级编号 cid 主键 ，班级名称cname 非空；
mysql> create table class(cid int primary key,cname varchar(10) not null);
Query OK, 0 rows affected (0.01 sec)
mysql> desc class;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cid | int(11) | NO | PRI | | |
| cname | varchar(10) | NO | | | |
+-------+-------------+------+-----+---------+-------+
3.给学生表添加一个字段：住址address;
mysql> alter table student add column address varchar(10);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
这是增加的字段address：
mysql> desc student;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(10) | NO | | | |
| sex | varchar(10) | NO | | | |
| birthday | date | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| cid | int(11) | YES | | NULL | |
| address | varchar(10) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)
4.修改学生表性别字段为gender;
mysql> alter table student change sex gender varchar(10);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
修改后的student表：
mysql> desc student;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(10) | NO | | | |
| gender | varchar(10) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| cid | int(11) | YES | | NULL | |
| address | varchar(10) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)
5.给birthday添加默认值为2000-01-01；
mysql> alter table student modify birthday date default '2000-01-01';
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
改变birthday的默认值：
mysql> desc student;
+----------+-------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+------------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(10) | NO | | | |
| gender | varchar(10) | YES | | NULL | |
| birthday | date | YES | | 2000-01-01 | |
| age | int(11) | YES | | NULL | |
| cid | int(11) | YES | | NULL | |
| address | varchar(10) | YES | | NULL | |
+----------+-------------+------+-----+------------+----------------+
6.给性别字段添加默认值为男；
mysql> set names gbk;
Query OK, 0 rows affected (0.02 sec)
mysql> alter table student modify gender varchar(10) default '男';
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+----------+-------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+------------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(10) | NO | | | |
| gender | varchar(10) | YES | | 男 | |
| birthday | date | YES | | 2000-01-01 | |
| age | int(11) | YES | | NULL | |
| cid | int(11) | YES | | NULL | |
| address | varchar(10) | YES | | NULL | |
+----------+-------------+------+-----+------------+----------------+
7.删除age字段；
mysql> alter table student drop age;
Query OK, 0 rows affected (0.01 sec)
mysql> desc student;
+----------+-------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+------------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(10) | NO | | | |
| gender | varchar(10) | YES | | 男 | |
| birthday | date | YES | | 2000-01-01 | |
| cid | int(11) | YES | | NULL | |
| address | varchar(10) | YES | | NULL | |
+----------+-------------+------+-----+------------+----------------+
9.给班级表插入3个班级；
mysql> insert into class values(1001,'一班');
Query OK, 1 row affected (0.03 sec)
mysql> insert into class values(1002,'二班');
Query OK, 1 row affected (0.00 sec)
mysql> insert into class values(1003,'三班');
Query OK, 1 row affected (0.00 sec)
mysql> select * from class;
+------+-------+
| cid | cname |
+------+-------+
| 1001 | 一班 |
| 1002 | 二班 |
| 1003 | 三班 |
+------+-------+
10.在学生表中给每个班级添加3名学生信息；
mysql> insert into student values(1,'张三','男','2012-01-02',1001,'cq');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(null,'李四','女','2009-03-22',1002,'cq');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(null,'王五','男','2009-03-25',1003,'cq');
Query OK, 1 row affected (0.00 sec)
mysql> select *from student;
+-----+-------+--------+------------+------+---------+
| sid | sname | gender | birthday | cid | address |
+-----+-------+--------+------------+------+---------+
| 1 | 张三 | 男 | 2012-01-02 | 1001 | cq |
| 2 | 李四 | 女 | 2009-03-22 | 1002 | cq |
| 3 | 王五 | 男 | 2009-03-25 | 1003 | cq |
+-----+-------+--------+------------+------+---------+
8.给学生表的cid上添加外键约束，以参考class表中的cid字段；
mysql> alter table student add constraint fk_cid foreign key(cid) references class(cid);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
受到约束报错：
mysql> insert into student values(null,'王五','男','2009-03-25',1004,'cq');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`c17/student`, CONSTRAINT `fk_cid` FOREIGN KEY (`cid`) REFERENCES `class`
(`cid`))
11.删除外键；
mysql> alter table student drop foreign key fk_cid;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
删除后可以往student继续添加学生信息：
mysql> insert into student values(null,'王五','男','2009-03-25',1004,'cq');
Query OK, 1 row affected (0.00 sec)
mysql> select *from student;
+-----+-------+--------+------------+------+---------+
| sid | sname | gender | birthday | cid | address |
+-----+-------+--------+------------+------+---------+
| 1 | 张三 | 男 | 2012-01-02 | 1001 | cq |
| 2 | 李四 | 女 | 2009-03-22 | 1002 | cq |
| 3 | 王五 | 男 | 2009-03-25 | 1003 | cq |
| 4 | 王五 | 男 | 2009-03-25 | 1004 | cq |
+-----+-------+--------+------------+------+---------+
4 rows in set (0.00 sec)
12.复制student表到student2；
mysql> create table student2 select * from student;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select *from student2;
+-----+-------+--------+------------+------+---------+
| sid | sname | gender | birthday | cid | address |
+-----+-------+--------+------------+------+---------+
| 1 | 张三 | 男 | 2012-01-02 | 1001 | cq |
| 2 | 李四 | 女 | 2009-03-22 | 1002 | cq |
| 3 | 王五 | 男 | 2009-03-25 | 1003 | cq |
| 4 | 王五 | 男 | 2009-03-25 | 1004 | cq |
+-----+-------+--------+------------+------+---------+
13.将表student2的名字改为stu;
改前所有的表：
mysql> show tables;
+---------------+
| Tables_in_c17 |
+---------------+
| class |
| student |
| student2 |
+---------------+
改后所有的表：
mysql> show tables;
+---------------+
| Tables_in_c17 |
+---------------+
| class |
| stu |
| student |
+---------------+
14.删除stu表中sid的自增长；
mysql> desc stu;
+----------+-------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+------------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(10) | NO | | | |
| gender | varchar(10) | YES | | 男 | |
| birthday | date | YES | | 2000-01-01 | |
| cid | int(11) | YES | | NULL | |
| address | varchar(10) | YES | | NULL | |
+----------+-------------+------+-----+------------+----------------+
mysql> alter table stu modify sid int;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> desc stu;
+----------+-------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+------------+-------+
| sid | int(11) | NO | PRI | 0 | |
| sname | varchar(10) | NO | | | |
| gender | varchar(10) | YES | | 男 | |
| birthday | date | YES | | 2000-01-01 | |
| cid | int(11) | YES | | NULL | |
| address | varchar(10) | YES | | NULL | |
+----------+-------------+------+-----+------------+-------+
15.删除stu表；
删除前：
mysql> show tables;
+---------------+
| Tables_in_c17 |
+---------------+
| class |
| stu |
| student |
+---------------+
mysql> drop table stu;
Query OK, 0 rows affected (0.01 sec)
删除后：
mysql> show tables;
+---------------+
| Tables_in_c17 |
+---------------+
| class |
| student |
+---------------+
2 rows in set (0.00 sec)
2.emp表
-- 1.查询员工表中 工资大于15000 且在2005年前入职的所有员工信息。
SELECT * FROM emp WHERE sal>15000 AND hiredate<'2005';
-- 2.查询销售员中工资最高的员工。
SELECT * FROM emp WHERE job='销售员' ORDER BY sal DESC LIMIT 1;
-- 3.查询文员中工资最低的员工信息。
SELECT * FROM emp WHERE job='文员' ORDER BY sal ASC LIMIT 1;
-- 4.查询姓张的员工中工资大于20000的员工信息。
SELECT * FROM emp WHERE ename LIKE '张%' AND sal>20000;
-- 5.查询工资最高的前3名员工信息。
SELECT * FROM emp ORDER BY sal DESC LIMIT 3;
-- 6.查询总工资最高的员工信息。
SELECT *,sal+IFNULL(comm,0) ss FROM emp ORDER BY ss DESC LIMIT 1;
-- 7.查询有津贴且在2001-02-01前入职的员工信息。
SELECT * FROM emp WHERE comm IS NOT NULL AND hiredate >'2001-02-01';
-- 8.查询员工的姓名、部门、总工资。
SELECT ename ,deptno,sal+IFNULL(comm,0) ss FROM emp;
-- 9.查询名字为三个字的工资最高的前2名员工信息。
SELECT * FROM emp WHERE ename LIKE '___' ORDER BY sal DESC LIMIT 2;
-- 10.查询职位为文员或销售员中工资最高的员工信息，工资要带￥符号。
SELECT empno ,ename ,job,CONCAT('￥',sal) 工资 FROM emp
WHERE job ='文员' OR job='销售员' ORDER BY sal DESC LIMIT 1;
--
ctrl + shift +c 加注释
ctrl + shift +r 去掉注释
3.-- emp表
SELECT*FROM emp;
-- 1.查询经理中入职时间最早的员工信息。
SELECT*FROM emp WHERE job='经理'ORDER BY hiredate ASC LIMIT 1;
-- 2.查询入职时间在2002-2004年的 且有津贴补助的员工信息。
SELECT*FROM emp WHERE comm IS NOT NULL AND hiredate >='2002'AND hiredate <='2004';
-- 3.查询30部门中名字带木的员工信息。
SELECT*FROM emp WHERE ename LIKE '%木%'AND deptno=30;
-- 4.查询名字为2个字，工资最高的2名员工信息。
SELECT*FROM emp WHERE ename LIKE '__' ORDER BY sal DESC LIMIT 2;
-- 5.查询工资小于10000或工资大于30000的员工的信息。
SELECT*FROM emp WHERE sal<10000 OR sal>30000;
-- 6.查询没有津贴的员工中，工资最低的2名员工的信息。
SELECT*FROM emp WHERE comm IS NULL ORDER BY sal ASC LIMIT 2;
-- 7.查询文员中入职最早的员工信息。
SELECT*FROM emp WHERE job='文员' ORDER BY hiredate ASC LIMIT 1;
-- 8.查询销售员中入职最晚的员工信息。
SELECT*FROM emp WHERE job='销售员' ORDER BY hiredate DESC LIMIT 1;
-- 9.查询名字为3个字，没有津贴，工资最低的员工信息。
SELECT*FROM emp WHERE ename LIKE '___' AND comm IS NULL ORDER BY sal ASC LIMIT 1;
-- 10.查询10号或20号部门中工资最低的员工的姓名、职位、部门、工资。
SELECT ename,job,deptno,sal FROM emp WHERE deptno=10 OR deptno=20 ORDER BY sal ASC LIMIT 1;
-- update emp表
-- 11.把名字为3个字的员工的津贴都改为100元。
UPDATE emp SET comm=100 WHERE ename LIKE '___';
-- 12.把文员的工资都涨100元。
UPDATE emp SET sal=sal+100 WHERE job='文员';
-- 13.把销售员中入职最早的员工的津贴增加200元。
UPDATE emp SET comm=comm+200 WHERE job='销售员' ORDER BY hiredate ASC LIMIT 1;
-- 14.把所有员工中入职最早的员工的工资增加200元。
UPDATE emp SET sal=sal+200 ORDER BY hiredate ASC LIMIT 1;
-- 15.把2002-2006年间入职的员工的津贴降低50元。
UPDATE emp SET comm=comm-50 WHERE hiredate BETWEEN '2002' AND '2006';
-- 16.把30号部门的所有员工的工资改为8000元，津贴改为500元。
UPDATE emp SET sal=8000,comm=500 WHERE deptno=30;
-- emp表
-- 17.删除工资最高的员工的信息。
ALTER TABLE emp DROP FOREIGN KEY fk_emp;
DELETE FROM emp ORDER BY sal DESC LIMIT 1;
-- 18.删除工资最低的员工的信息。
DELETE FROM emp ORDER BY sal ASC LIMIT 1;
-- 19.删除 没有津贴，名字为2个字的员工信息。
DELETE FROM emp WHERE comm IS NULL AND ename LIKE '__';
-- 20.删除30号部门中入职时间早的员工信息。
DELETE FROM emp WHERE deptno=30 ORDER BY hiredate ASC LIMIT 1;
-- 21.删除所有员工中名字带张的员工的信息。
DELETE FROM emp WHERE ename LIKE '%张%';
SELECT*FROM emp;
/*创建部门表*/
CREATE TABLE dept(
deptno INT PRIMARY KEY,
dname VARCHAR(50),
loc VARCHAR(50)
);
/*创建雇员表*/
CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT,
CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno)
);
/*创建工资等级表*/
CREATE TABLE salgrade(
grade INT PRIMARY KEY,
losal INT,
hisal INT
);
/*插入dept表数据*/
INSERT INTO dept VALUES (10, '教研部', '重庆');
INSERT INTO dept VALUES (20, '学工部', '成都');
INSERT INTO dept VALUES (30, '销售部', '北京');
INSERT INTO dept VALUES (40, '财务部', '天津');
/*插入emp表数据*/
INSERT INTO emp VALUES (1009, '习近平', '董事长', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO emp VALUES (1004, '范冰冰', '经理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO emp VALUES (1006, '李冰冰', '经理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO emp VALUES (1007, '高圆圆', '经理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO emp VALUES (1008, '周星驰', '分析师', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO emp VALUES (1013, '周星星', '分析师', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO emp VALUES (1002, '周六福 ', '销售员', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO emp VALUES (1003, '康熙', '销售员', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO emp VALUES (1005, '李世民 ', '销售员', 1006, '2001-09-28', 12500, 4000, 30);
INSERT INTO emp VALUES (1010, '孙悟空', '销售员', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO emp VALUES (1012, '唐僧', '文员', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO emp VALUES (1014, '令狐冲', '文员', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO emp VALUES (1011, '刘欢', '文员', 1008, '2007-05-23', 11000, NULL, 20);
INSERT INTO emp VALUES (1016, '小沈阳', '文员', 1007, '2008-05-23', 14000, NULL, 20);
INSERT INTO emp VALUES (1015, '张浩', '销售员', 1006, '2001-08-08', 17000, 0, 30);
INSERT INTO emp VALUES (1001, '朱元璋', '文员', 1013, '2000-12-17', 8000, NULL, 20);
/*插入salgrade表数据*/
INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);
SELECT*FROM emp;
网友评论