sql语言的各种增删改查操作

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sql语言
数据定义语言(DDL)，数据操作语言(DML)，数据控制语言(DCL)和事务控制语言（TCL）。
数据定义语言是对表进行操作：create、drop、alter
数据操作语言是对数据进行操作：insert、update、delete
数据控制语言是对用户权限进行操作：grant，revoke
数据查询功能：select
   primary key 主键约束（主码约束）
   unique       唯一性约束
   not null     非空约束
   check       取值约束

对表的操作
创建表
mysql> create table student(
    -> id varchar(10) primary key,//为id加上主键约束
    -> name varchar(10),
    -> chengji int(4));
Query OK, 0 rows affected (0.02 sec)

删除表
mysql> drop table student;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)

对表中列的操作
列的曾删改
mysql> alter table student
   -> add passwod varchar(20) not null,//增加新的列名
    -> drop password,                   //删除列名
    -> modify chengji int(5) not null; //修改列名的数据类型和约束条件
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

对表中数据的操作
给表中添加内容
mysql> insert into student()
-> value('16033001','wang',90),('16033002','li',99);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

使用子查询给表中添加内容
mysql>insert into 1111
    ->select * from student
    ->WHERE ID IN (SELECT ID
                  FROM student);
修改表中内容
mysql> update student set name='xiao' where id='16033011';
Query OK, 1 row affected (0.00 sec)

使用子查询修改表中内容
mysql> update student set name='xiao' where id in (select id from test.user);
Query OK, 2 rows affected (0.02 sec)

删除表中的内容
mysql> delete from student where id='16033011';
Query OK, 1 row affected (0.00 sec)

使用子查询删除表中内容
mysql> delete from student where id in (select id from test.user);
Query OK, 2 rows affected (0.02 sec)

查询表中内容
mysql> select * from student;
+----------+------+---------+
| id       | name | chengji |
+----------+------+---------+
| 16033001 | wang |      90 |
| 16033002 | li   |      99 |
+----------+------+---------+
2 rows in set (0.00 sec)

范围查询
mysql> select * from student where chengji between 90 and 90;
+----------+------+---------+
| id | name | chengji |
+----------+------+---------+
| 16033001 | xiao | 90 |
+----------+------+---------+
1 row in set (0.00 sec)

模糊查询
通配符： %    替代 0 个或多个字符
       _    替代一个字符
          字符列中的任何单一字符
       详情请点击下面链接
       https://www.runoob.com/sql/sql-wildcards.html
mysql> select * from student where id like '160%';
+----------+------+---------+
| id       | name | chengji |
+----------+------+---------+
| 16033001 | xiao |      90 |
| 16033002 | xiao |      99 |
+----------+------+---------+
2 rows in set (0.00 sec)

order by 排序
升序
mysql> select * from student order by chengji asc;
+----------+------+---------+
| id       | name | chengji |
+----------+------+---------+
| 16033001 | xiao |      90 |
| 16033002 | xiao |      99 |
+----------+------+---------+
2 rows in set (0.00 sec)
降序
mysql> select * from student order by chengji desc;
+----------+------+---------+
| id       | name | chengji |
+----------+------+---------+
| 16033002 | xiao |      99 |
| 16033001 | xiao |      90 |
+----------+------+---------+
2 rows in set (0.00 sec)

limit用法
mysql> select * from sql1.class;
+-----+-----------+
| cid | classname |
+-----+-----------+
| 01 | yiban     |
| 02 | erban     |
| 03 | sanban    |
| 04 | siban     |
+-----+-----------+
4 rows in set (0.00 sec)

mysql> select * from sql1.class limit 0,2; //'0'代表开始的行，'2'代表显示的行数
+-----+-----------+
| cid | classname |
+-----+-----------+
| 01 | yiban |
| 02 | erban |
+-----+-----------+
2 rows in set (0.00 sec)

mysql> select * from sql1.class limit 1,2;
+-----+-----------+
| cid | classname |
+-----+-----------+
| 02 | erban |
| 03 | sanban |
+-----+-----------+
2 rows in set (0.00 sec)

使用统计函数
   常见的统计函数
   计数：count
   计算和：sum
   计算平均值：avg
   求最大值：max
   求最小值：min
如：
mysql> select max(chengji) from student;
+--------------+
| max(chengji) |
+--------------+
|           99 |
+--------------+
1 row in set (0.00 sec)
mysql> select * from student where chengji in (select max(chengji) from student);
+----------+------+---------+
| id       | name | chengji |
+----------+------+---------+
| 16033002 | xiao |      99 |
+----------+------+---------+
1 row in set (0.00 sec)

对查询结果进行分组
如:您想了解每个客户的工资总额，则可使用 GROUP BY 查询，如下所示：
mysql> select name, sum(gongzi) from company group by name;

连接查询
同时涉及多个表的查询是连接查询
mysql> select * from student;
+----------+------+---------+
| id       | name | chengji |
+----------+------+---------+
| 16033001 | xiao |      90 |
| 16033002 | wang |      99 |
+----------+------+---------+
2 rows in set (0.00 sec)

mysql> select * from class;
+-----+-----------+
| cid | classname |
+-----+-----------+
| 01 | yiban |
| 02 | erban |
+-----+-----------+
2 rows in set (0.00 sec)

广义笛卡尔积：
mysql> select student.*,class.* from class,student;
+----------+------+---------+-----+-----------+
| id       | name | chengji | cid | classname |
+----------+------+---------+-----+-----------+
| 16033001 | xiao |      90 | 01 | yiban     |
| 16033001 | xiao |      90 | 02 | erban     |
| 16033002 | wang |      99 | 01 | yiban     |
| 16033002 | wang |      99 | 02 | erban     |
+----------+------+---------+-----+-----------+
4 rows in set (0.00 sec)
等值与非等值连接查询
等值连接查询：where或having等匹配条件为 = 的连接查询
如：select student.*,class.* from class,student where student.chengji=class.changji
自然查询：是等值查询的一种特殊情况，把查询的列中重复的列去掉（将'*'改为具体的列名可实现自然查询）
非等值查询：where或having等匹配条件不为 = 的连接查询

自连接查询：一个表与自己进行连接的查询，需要给表起别名进行区分（别名不能以数字开头）
mysql> select yi.id,er.id from student yi,student er;
+----------+----------+
| id | id |
+----------+----------+
| 16033001 | 16033001 |
| 16033002 | 16033001 |
| 16033001 | 16033002 |
| 16033002 | 16033002 |
+----------+----------+
4 rows in set (0.00 sec)

外连接查询（outer join）
普通连接（内连接）操作只输出满足连接条件的行，外连接以指定表为连接主体 access_log.site_id（左表）=Websites.id（右表）
右外连接：连接符右边的表返回所有的行，即使左表中没有匹配。如果左表中没有匹配，则结果为 NULL
左外连接：连接符左边的表返回所有的行，即使右表中没有匹配。如果右表中没有匹配，则结果为 NULL
以右链接为例
Websites表数据
+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google       | https://www.google.cm/    | 1     | USA     |
| 2 | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3 | 菜鸟教程      | http://www.runoob.com/    | 4689 | CN      |
| 4 | 微博          | http://weibo.com/         | 20    | CN      |
| 5 | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7 | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+
access_log表数据
+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)

SELECT Websites.name, access_log.count, access_log.date
FROM access_log
RIGHT JOIN Websites
ON access_log.site_id=Websites.id
+---------+-------+------------+
| name | count | date       |
+---------+-------+------------+
| Google|    45 | 2016-05-10 |
| 菜鸟教程 |   100 | 2016-05-13 |
| Google |   230 | 2016-05-14 |
| 淘宝 |    10 | 2016-05-14 |
| Facebook |   205 | 2016-05-14 |
| 微博 |    13 | 2016-05-15 |
| 菜鸟教程 |   220 | 2016-05-15 |
| Facebook |   545 | 2016-05-16 |
| 菜鸟教程 |   201 | 2016-05-17 |
|stackoverflow|   null | null |
+---------+-------+------------+
10 rows in set (0.00 sec)

复合条件连接查询
当where后面还有多个连接条件时，称为复合条件连接查询，不同的连接条件用and或or连接

集合查询（union ）（使用时必须列数一致（如下面例子都是两列））
默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL
mysql> select * from student
-> ;
+----------+------+
| id | name |
+----------+------+
| 16033001 | xiao |
| 16033002 | wang |
+----------+------+
2 rows in set (0.00 sec)

mysql> select * from class;
+-----+-----------+
| cid | classname |
+-----+-----------+
| 01 | yiban |
| 02 | erban |
+-----+-----------+
2 rows in set (0.00 sec)

mysql> select * from student union   select * from class;
+----------+-------+
| id       | name |
+----------+-------+
| 16033001 | xiao |
| 16033002 | wang |
| 01       | yiban |
| 02       | erban |
+----------+-------+
4 rows in set (0.00 sec)