SQL基础基本操作

sql操作分类

1.DDL 数据定义语言 指对数据库、表、列等的操作
关键字 create 、drop、alter
2.DML 数据操作语言 指对数据库中的表的增删改操作
关键字 insert、delete、update
3.DQL 数据库查询语言 指对数据库中的表的查询操作
关键字 select、where
4.DCL 数据库控制语言 指对数据库的访问权限和安全级别的操作
关键字 crant、revoke

常用操作

DDL

只记录日常用的多的,由于初学接触不到这么多复杂表结构、导致之前学的完全忘了
纯手打 可能会出现单词拼写错误

---

数据库常用

例子
1.创建一个test数据库,并默认字符编码为GBK（为了效率一般这里创建之前进行判断该数据库是否已存在）
create database if not exists test default charset gbk;
2.查询全部数据库
show databases;
3.查询建表语句(主要查看设置字符集是否生效了)
show create database test;
4.修改test数据库的默认字符集为UTF-8,并查看是否生效
alter database test default charset utf8;
show create database test;
5.使用数据库test,并查看当前正在使用的数据库
use test;
select database();
6.删除test
drop database if exists test;
结果如下 (默认和修改数据库和表编码还有一种写法是 character set 字符集)
还有一个查询MySQL的全部字符集命令(乱码可以查看这条命令,如果哪里没设置通过修改配置文件即可,
配置文件在根目录的my.nin文件中，如果没有打开隐藏文件夹，C盘下找programData->mysql->my.nin)
show variables like '%char%';


演示结果
mysql> create database if not exists test default charset gbk; -- 创建数据库并默认字符集
Query OK, 1 row affected (0.00 sec)

mysql> show databases; -- 显示所有数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> show create database test; -- 查询键数据库语句
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter database test default charset utf8; -- 修改字符集
Query OK, 1 row affected (0.00 sec)

mysql> show create database test; -- 查询建数据库语句
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> use test;  --使用数据库
Database changed
mysql> select database();  -- 查看正在使用的数据库
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)
mysql> drop database if exists test; -- 删除数据库
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%char%'; -- 查询所有相关字符集 乱码重点看星号部分
+--------------------------+--------------------------+
| Variable_name            | Value                    |
+--------------------------+--------------------------+
| character_set_client     | utf8                     |
| character_set_connection | utf8                     |
| character_set_database   | utf8                     | ****
| character_set_filesystem | binary                   |
| character_set_results    | utf8                     |
| character_set_server     | utf8                     | ****
| character_set_system     | utf8                     |
| character_sets_dir       | E:\Mysql\share\charsets\ |
+--------------------------+--------------------------+
8 rows in set (0.00 sec)

表常用

常见表的字段类型
int 整数类型
varchar 字符串
double 浮点数
date 日期  格式 yyyy-MM-dd
datetime 日期  格式 yyyy-MM-dd HH:mm:ss
timestamp 时间戳 格式 yyyy-MM-dd HH:mm:ss 如果不赋值 默认当前时间

例子
1.创建一个user表,id为整数类型,age为一位小数的浮点型,姓名为字符串类型，生日为日期型，
插入时间为时间戳类型并默认字符集为GBK
create table if not exists user(
	id int,
	age double(4,1),
	name varchar(10),
	birthday date,
	insert_time timestamp) default charset gbk;
2.修改默认字符集为UTF-8,并查看建表语句
alter table user default charset utf8;
show create table user;
3.插入gender性别字段为字符串,并查看表结构
alter table user add gender varchar(3);
desc user;
 ** 如果添加多列 add 列名 字段类型,add 列名 字段类型
4.修改gender字段名为sex 字段长度最大为5，并查看修改结果
alter table user change gender sex varchar(5);
desc user;
5.修改表名为student，并查看结果
 alter table user rename to student;
 show tables;
6.删除sex列
alter table student drop sex;
** 如果删除多列 drop column 列名,drop column 列名
7.在test2数据库中复制一份与student表结构相同的表,查看结果
use test2;
create table if not exists stu like test.student;
desc stu；
8.删除stu表,查看结果
drop table stu;
show tables;


演示结果
mysql> create table if not exists user(  --建表语句
    -> id int,
    -> age double(4,1),  -- 第一个参数为总长度,第二个参数为小数点位数
    -> name varchar(10),
    -> birthday date,
    -> insert_time timestamp) default charset gbk;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table user default charset utf8; -- 修改字符集
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table user;  -- 查看建表语句
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                            |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL,
  `age` double(4,1) DEFAULT NULL,
  `name` varchar(10) CHARACTER SET gbk DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table user add gender varchar(3);  -- 添加表中字段
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;       --查看表结构
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field       | Type        | Null | Key | Default           | Extra                       |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| id          | int(11)     | YES  |     | NULL              |                             |
| age         | double(4,1) | YES  |     | NULL              |                             |
| name        | varchar(10) | YES  |     | NULL              |                             |
| birthday    | date        | YES  |     | NULL              |                             |
| insert_time | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| gender      | varchar(3)  | YES  |     | NULL              |                             |
+-------------+-------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.03 sec)

mysql> alter table user change gender sex varchar(5);  -- 修改表中字段
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;     -- 查看表结构
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field       | Type        | Null | Key | Default           | Extra                       |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| id          | int(11)     | YES  |     | NULL              |                             |
| age         | double(4,1) | YES  |     | NULL              |                             |
| name        | varchar(10) | YES  |     | NULL              |                             |
| birthday    | date        | YES  |     | NULL              |                             |
| insert_time | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| sex         | varchar(5)  | YES  |     | NULL              |                             |
+-------------+-------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.02 sec)

mysql> alter table user rename to student;  -- 修改表名
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;  -- 查看数据库中的所有表
+----------------+
| Tables_in_test |
+----------------+
| student        |
+----------------+
1 row in set (0.00 sec)

mysql> alter table student drop sex;    -- 删除表中字段
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> use test2;   -- 切换数据库
Database changed
mysql> create table if not exists stu like test.student; 
									 -- 复制表结构 如果在同一个数据库下直接写表名
Query OK, 0 rows affected (0.01 sec)

mysql> desc stu;   -- 查看表结构
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field       | Type        | Null | Key | Default           | Extra                       |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| id          | int(11)     | YES  |     | NULL              |                             |
| age         | double(4,1) | YES  |     | NULL              |                             |
| name        | varchar(10) | YES  |     | NULL              |                             |
| birthday    | date        | YES  |     | NULL              |                             |
| insert_time | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.03 sec)

mysql> drop table stu;  -- 删除表
Query OK, 0 rows affected (0.00 sec)

mysql> show tables; -- 查看数据库下所有表
Empty set (0.00 sec)

DML

以刚才的user表为基础

举例说明
1.插入一条id为2 birthday为1998-10-10的数据
insert into user (id,birthday) values (2,'1998-10-10');
** 如果表名后没有字段名,则values后面与表结构一一对应
insert into user values(1,20,'张三','1998-10-11',null);
2.修改id为2的name值为李四,age为14,并查看结果
update user set name='李四',age=14 where id=2; 
select * from user;
3.删除id为2的数据
delete from user where id=2; -- 如果不加条件删除所有
** 如果以命令行执行可能会出现乱码,命令行窗口默认gbk编码 
** 执行 set names gbk; 即可 可以使用show variables like '%char%';查看变化

结果示例
mysql> insert into user (id,birthday) values(2,'1998-10-10');  -- 插入数据
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(1,20,'张三','1998-10-11',null); -- 插入数据
Query OK, 1 row affected (0.00 sec)

mysql> update user set name='李四',age=14 where id=2;  -- 修改id为2的数据,不加条件修改所有
Query OK, 1 row affected (1.63 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;  -- 查询表中全部数据
+------+------+------+------------+---------------------+
| id   | age  | name | birthday   | insert_time         |
+------+------+------+------------+---------------------+
|    2 | 14.0 | 李四     | 1998-10-10 | 2020-07-25 18:07:35 |
|    1 | 20.0 | 张三     | 1998-10-11 | 2020-07-25 18:07:00 |
+------+------+------+------------+---------------------+
2 rows in set (0.00 sec)

mysql> delete from user where id=2;  -- 删除id为2的列语句
Query OK, 1 row affected (0.00 sec)

mysql> select * from user; -- 查询表中全部数据
+------+------+------+------------+---------------------+
| id   | age  | name | birthday   | insert_time         |
+------+------+------+------------+---------------------+
|    1 | 20.0 | 张三     | 1998-10-11 | 2020-07-25 18:07:00 |
+------+------+------+------------+---------------------+
1 row in set (0.00 sec)

DQL

语法 select 字段列表 from 表名列表 where 条件列表 group by 分组 
			having 分组后条件 order by 排序 limit 分页限定
			
+------+------+------+------+---------+------+---------+
| id   | age  | name | sex  | address | math | chinese |
+------+------+------+------+---------+------+---------+
|    1 | 20.0 | 张三     | 男     | 河南        | 40.0 |    45.0 |
|    2 | 40.0 | 李四     | 女    | 北京        | 50.0 |    40.0 |
|    3 | 18.0 | 王五     | 男     | 北京        | 50.0 |    45.0 |
|    4 | 20.0 | 赵六     | 男     | 深圳        | 45.0 |    64.0 |
|    5 | 13.0 | 李琦     | 男     | 上海       | 40.0 |    NULL |
|    6 | 12.0 | 周州     | 女    | 广州        | 20.0 |    64.0 |
|    7 | 19.0 | 唐堂     | 女    | 上海       | 50.0 |    80.0 |
+------+------+------+------+---------+------+---------+
以这张表为例
1.查询name和address字段 
select name,address from user;
2.查询全部address去重
select distinct adress from user;
 ** distinct 为select选项去重 默认为all
3.查询name,math,chinese 起中文别名 并计算总分
select name as 姓名,math as 数学,chinese as 语文,math+ifnull(chinese,0) as 总分 from user;
 ** 别名as 可省略 
 ** ifnull()函数 第一个参数为字段名 第二个为替换值 如果为空用第二个替换

结果演示
mysql> select name,address from user;  -- 查询多列
+------+---------+
| name | address |
+------+---------+
| 张三     | 河南        |
| 李四     | 北京        |
| 王五     | 北京        |
| 赵六     | 深圳        |
| 李琦     | 上海       |
| 周州     | 广州        |
| 唐堂     | 上海       |
+------+---------+
7 rows in set (0.00 sec)

mysql> select distinct address from user;  -- 去重查询
+---------+
| address |
+---------+
| 河南        |
| 北京        |
| 深圳        |
| 上海       |
| 广州        |
+---------+
5 rows in set (0.00 sec)

mysql> select name as 姓名,math as 数学,chinese as 语文,math+ifnull(chinese,0) as 总分 from user;
				-- 总分和别名查询并显示
+------+------+------+-------+
| 姓名     | 数学    | 语文     | 总分     |
+------+------+------+-------+
| 张三     | 40.0 | 45.0 |  85.0 |
| 李四     | 50.0 | 40.0 |  90.0 |
| 王五     | 50.0 | 45.0 |  95.0 |
| 赵六     | 45.0 | 64.0 | 109.0 |
| 李琦     | 40.0 | NULL |  40.0 |
| 周州     | 20.0 | 64.0 |  84.0 |
| 唐堂     | 50.0 | 80.0 | 130.0 |
+------+------+------+-------+
7 rows in set (0.00 sec)


条件查询
where后面所跟得条件表达式 
and、or、not、is null、between..and、in()、<> (不等于)、like
 ** like 模糊查询 字段名=条件 使用占位符来 _代表一个字符 %多个字符
 ** is null 判断是不是空 字段名 is null
 ** in() 相当于多个or连接的条件
 ** between..and 取两者条件之间的数据 
举例
1.查询数学成绩大于等于20并且小于等于40的人的姓名
select name,math from user where math between 20 and 40;
 ** 或者select name,math from user where math >=20 and math<=40;
2.查询名字中包含赵或者李的，并且语文成绩不为空
select * from user where name like '%赵%' or name like '%李%' and chinese is not null;
3.查询数学成绩为30或40或50的人
select name,math from user where math in (30,40,50);

结果演示
mysql> select name,math from user where math between 20 and 40; --第一条
+------+------+
| name | math |
+------+------+
| 张三     | 40.0 |
| 李琦     | 40.0 |
| 周州     | 20.0 |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from user where name like '%赵%' or name like '%李%' and chinese is not null;
	-- 第二条
+------+------+------+------+---------+------+---------+
| id   | age  | name | sex  | address | math | chinese |
+------+------+------+------+---------+------+---------+
|    2 | 40.0 | 李四     | 女    | 北京        | 50.0 |    40.0 |
|    4 | 20.0 | 赵六     | 男     | 深圳        | 45.0 |    64.0 |
+------+------+------+------+---------+------+---------+
2 rows in set (0.00 sec)

mysql> select name,math from user where math in(30,40,50);
	-- 第三条
+------+------+
| name | math |
+------+------+
| 张三     | 40.0 |
| 李四     | 50.0 |
| 王五     | 50.0 |
| 李琦     | 40.0 |
| 唐堂     | 50.0 |
+------+------+
5 rows in set (0.00 sec)


分组,分页，排序和聚合查询
以这张表为例
mysql> select * from user;
+------+------+------+------+---------+------+---------+
| id   | age  | name | sex  | address | math | chinese |
+------+------+------+------+---------+------+---------+
|    1 | 20.0 | 张三     | 男     | 河南        | 40.0 |    45.0 |
|    2 | 40.0 | 李四     | 女    | 北京        | 50.0 |    40.0 |
|    3 | 18.0 | 王五     | 男     | 北京        | 50.0 |    45.0 |
|    4 | 20.0 | 赵六     | 男     | 深圳        | 45.0 |    64.0 |
|    5 | 13.0 | 李琦     | 男     | 上海       | 40.0 |    NULL |
|    6 | 12.0 | 周州     | 女    | 广州        | 20.0 |    64.0 |
|    7 | 19.0 | 唐堂     | 女    | 上海       | 50.0 |    80.0 |
|    8 | 12.0 | 李岩     | 男     | 上海       | 80.0 |    40.0 |
|    9 | 13.0 | 李燕     | 女    | 上海       | 40.0 |    60.0 |
+------+------+------+------+---------+------+---------+
9 rows in set (0.00 sec)

举例说明
1.查询字段name，math,chinese并且math升序，如果相同chinese降序
select name,math,chinese from user order by math ASC,chinese DESC;
 ** ASC 升序 默认 DESC 降序
2.用姓名进行分组,如果math成绩小于等于30不参与分组,查询出分组后的人数,math平均分
select sex,count(id),avg(math) from user where math>30 group by sex;
** 聚合函数 以一列为整体,进行纵向计算 (一般选择主键)
	** count(列名) 计算个数,碰见null略过 
	** avg(列名) 计算平均值 max(列名) 最大值 min() 最小值 sum() 求和
** group 分组字段 以某个字段进行分组 
** 如果分组后查询单个字段无任何意义
3.以姓名分组查询出分组后人数大于4,sex列和人数
select sex,count(id) as 人数 group by sex having 人数>4;
 ** 以分组为分隔 where 无法使用聚合函数为条件 having 可以
4.跳过四条,查询3条的name和id字段
select id,name from user limit 4,3;
 ** 分页查询  limit 跳过几条，查几条  

结果示例
mysql> select name,math,chinese from user order by math ASC,chinese DESC;
+------+------+---------+
| name | math | chinese |
+------+------+---------+
| 周州     | 20.0 |    64.0 |
| 李燕     | 40.0 |    60.0 |
| 张三     | 40.0 |    45.0 |
| 李琦     | 40.0 |    NULL |
| 赵六     | 45.0 |    64.0 |
| 唐堂     | 50.0 |    80.0 |
| 王五     | 50.0 |    45.0 |
| 李四     | 50.0 |    40.0 |
| 李岩     | 80.0 |    40.0 |
+------+------+---------+
9 rows in set (0.00 sec)

mysql> select sex,count(id),avg(math) from user where math>30 group by sex;
+------+-----------+-----------+
| sex  | count(id) | avg(math) |
+------+-----------+-----------+
| 女    |         3 |  46.66667 |
| 男     |         5 |  51.00000 |
+------+-----------+-----------+
2 rows in set (0.00 sec)

mysql> select sex,count(id) as sum from user group by sex having sum>4;
+------+-----+
| sex  | sum |
+------+-----+
| 男     |   5 |
+------+-----+
1 row in set (0.00 sec)

mysql> select id,name from user limit 4,3;
+------+------+
| id   | name |
+------+------+
|    5 | 李琦     |
|    6 | 周州     |
|    7 | 唐堂     |
+------+------+
3 rows in set (0.00 sec)