【SQL入门笔记1】通用语法及分类

MySQL的启动

// 设置环境变量
(base) xiaoxiaomo@MacBook-Pro ~ % PATH="$PATH":/usr/local/mysql/bin
// 启动并输入密码
// myspl [-h 127.0.0.1] [-p 3306] -u root -p
(base) xiaoxiaomo@MacBook-Pro ~ % mysql -u root -p

（一）SQL的分类

1）DDL操作

// 创建数据库，不存在则创建，存在也不报错
mysql> create database if not exists soil;
Query OK, 1 row affected, 1 warning (0.00 sec)
// 指定字符集，utf8直邮三个字节，utf8mb4是4个字节
mysql> create database itheima default charset utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)

// 数据库查询
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| soil               |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

// 删除数据库
mysql> drop database if exists soil;
Query OK, 0 rows affected (0.01 sec)

// 使用数据库
mysql> use itheima;
Database changed

// 查询当前数据库
mysql> select database();
+------------+
| database() |
+------------+
| itheima    |
+------------+
1 row in set (0.00 sec)

// 进入数据库中查询数据库中表格
mysql> show tables;
+-----------------------------------------------+
| Tables_in_sys                                 |
+-----------------------------------------------+
| host_summary                                  |
| host_summary_by_file_io                       |
| host_summary_by_file_io_type                  |
| host_summary_by_stages                        |
| host_summary_by_statement_latency             |
| host_summary_by_statement_type                |
| innodb_buffer_stats_by_schema                 |

1-1)表结构的创建

// DDL-表操作-创建
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.01 sec)

// 验证是否创建成功
mysql> show tables;
+----------------+
| Tables_in_soil |
+----------------+
| 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 create 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.00 sec)

1-2)表结构的修改删除

// DDL-表操作-添加字段
mysql> alter table tb_user add nickname varchar(20) comment '昵称';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
// 查看
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    |       |
| nickname | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

// DDL-表操作-修改字段
// 修改字段名和字段类型
mysql> alter table tb_user change nickname username varchar(30) comment '用户名';m

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    |       |
| username | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
// 删除
mysql> alter table tb_user drop username;

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.00 sec)

// 修改表名
mysql> alter table tb_user rename to user_tb;
mysql> show tables;
+----------------+
| Tables_in_soil |
+----------------+
| user_tb        |
+----------------+
1 row in set (0.01 sec)

// 删除表时表中的数据全部都会被删除掉
// 删除某个表
mysql> drop table if exists tb_user;
Query OK, 0 rows affected, 1 warning (0.00 sec)
// 删除指定表并重建该表
mysql> truncate table user_tb;

mysql> show tables;
+----------------+
| Tables_in_soil |
+----------------+
| user_tb        |
+----------------+
1 row in set (0.00 sec)

2）DML操作

2-1）DML增加

// 给指定字段添加数据
mysql> insert into users(id, name, age) values ('1','mordredl',10);
Query OK, 1 row affected (0.00 sec)
// 查看
mysql> select * from users;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | mordredl |   10 |
|    1 | mordredl |   10 |
+------+----------+------+
2 rows in set (0.00 sec)

// 给全部字段添加数据
mysql> insert into users values (3,'mordredl',10);
mysql> select * from users;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | mordredl |   10 |
|    1 | mordredl |   10 |
|    3 | mordredl |   10 |
+------+----------+------+
3 rows in set (0.00 sec)
// 批量添加数据
insert into users values (4,'mordredl',1),(5,'mordred',9),(6,'mordred',7);

2-2）DML修改

// 修改条件（id=1）的数据
update users set name='itheima' where id=1;
//修改多个
update users set name='itheima',age=7 where id=1;
// 修改所有条目的同一项
update users set age=7;

2-3）DML删除（逻辑同修改）

delete 不能删除某一个字段的值（可以用updata把该字段置为none）

// 删除数据
delete from users where id = '1';
//删除所有数据
delete from users;

3）DQL操作

3-1）DQL基本查询

// 查询指定字段
mysql> select id,name from users;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
|    3 | wangwu   |
|    4 | xiamu    |
|    5 | maomi    |
|    6 | 丙       |
|    1 | zhangsan |
|    2 | lisi     |
|    3 | wangwu   |
|    4 | xiamu    |
|    5 | maomi    |
|    6 | 丙       |
+------+----------+
12 rows in set (0.00 sec)
// 尽量不要写*，影响效率
mysql> select * from users;

// 查询并起别名
mysql> select name as '姓名' from users;
+----------+
| 姓名     |
+----------+
| zhangsan |
| lisi     |
| wangwu   |
| xiamu    |
| maomi    |
| 丙       |
| zhangsan |
| lisi     |
| wangwu   |
| xiamu    |
| maomi    |
| 丙       |
+----------+
// 查询不重复记录
mysql> select distinct name as '姓名' from users;
+----------+
| 姓名     |
+----------+
| zhangsan |
| lisi     |
| wangwu   |
| xiamu    |
| maomi    |
| 丙       |
+----------+

3-2）DQL条件查询

// 查询
mysql>select * from users where age = 1000;
mysql>select * from users where age != 1000;
mysql>select * from users where age < 1000;
mysql>select * from users where name is null;
mysql>select * from users where name is not null;
// 
mysql>select * from users where age <= 1000 && age >= 5;
mysql>select * from users where age <= 1000 and age >= 5;
mysql>select * from users where age between 5 and 1000;
// 列表中所写数值满足其一即可
mysql>select * from users where age in(7,16);
//
mysql>select * from users where name like '_____'
// 前面几个字符无所谓，只保证最后一个字符是X就行
mysql>select * from users where idcards like '%X'

3-3）DQL聚合函数

所有的null值不参与聚合函数计算

// An highlighted block
mysql> select avg(age) from users;
mysql> select avg(age) from users;
+-----------+
| avg(age)  |
+-----------+
| 1836.5000 |
+-----------+
mysql> select sum(age) from users where name like '_____';
+----------+
| sum(age) |
+----------+
|    20032 |
+----------+

3-4）DQL分组查询

// 根据性别分组
select gender,count(*) from users group by gender;
// 根据性别分组,并求各自的平均年龄
select gender,avg(age) from users group by gender;
// 查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workplace,count(*) from users where age <= 45 group by workplace having count(*) >= 3;

3-5）DQL排序查询

// An highlighted block
select * from users order by age asc;
select * from users order by age asc,entrydata desc;

3-6）DQL分页查询

3）DQL操作

3-1）DQL基本查询

// 用户管理
// 查询用户
use mysql;
select * from user;