mysql基础语句(上)
# SQL语句结构
结构化查询语言包含6个部分：
- 一：数据查询语言（DQL:Data Query Language）：
其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。
- 二：数据操作语言（DML：Data Manipulation Language）：
其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。
- 三：事务处理语言（TPL）：跟shell有点类似 由多条sql语句组成的整体
它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。
- 四：数据控制语言（DCL）：
它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
- 五：数据定义语言（DDL）：
其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。
- 六：指针控制语言（CCL）：
它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。([dɪˈkler] [ˈkɜ:rsə(r)]声明游标)[fɛtʃ] into获取到
---
# MySQL语句
## DDL语句之管理库
### 关于数据库的操作
命令行登录
```
[root@jkl1234 ~]# mysql -uroot -proot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 264
Server version: 8.0.24 Source distribution
......
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
```
常用参数
```
-h, --host=
-P, --port=
-e, --execute=name Execute command and quit.
```
**查看数据库：**
```
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
mysql> show databases \G #以行的方式显示
*************************** 1. row ***************************
Database: information_schema
*************************** 2. row ***************************
Database: mysql
*************************** 3. row ***************************
Database: performance_schema
*************************** 4. row ***************************
Database: sys
4 rows in set (0.00 sec)
```
注：
1：information_schema这数据库保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型不访问权限等。
2：performance_schema 这是MySQL5.5新增的一个性能优化的引擎：命名PERFORMANCE_SCHEMA ,主要用于收集数据库服务器性能参数。MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表
`http://www.cnblogs.com/hzhida/archive/2012/08/08/2628833.html`
3：mysql库是系统库，里面保存有账户信息，权限信息等。
4：mysql5.7增加了sys 系统数据库，通过这个库可以快速的了解系统的元数据信息元数据是关于数据信息的数据，如数据库名或表名，列的数据类型，或访问权限等。
---
mysql –e 后面我们接SQL语句，直接终端运行，后面写sql 相关shell可以用到
```
[root@jkl1234 ~]# mysql -uroot -proot -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
```
**创建数据库：**
语法：create database 数据库名;
创建数据库注意事项：
1)在文件系统中，MySQL的数据存储区将以目录方式表示MySQL数据库。因此，上面命令中的数据库名字必须与操作系统的约束的目录名字一致。例如不允许文件和目录名中有\,/,:,*,?,”,<,>,|这些符号，在MySQL数据库名字中这些字母会被自动删除。<遵从目录的约束>
2)数据库的名字不能超过64个字符，包含特殊字符的名字或者是全部由数字或保留字组成的名字必须用单引号``包起来。
3)数据库不能重名。
```
语法：
CREATE DATABASE dbanme;
# 指定字符集和校对规则建库
CREATE DATABASE db_name CHARACTER SET charset_name COLLATE collation_name
# 例子：
CREATE DATABASE oldboy;
CREATE DATABASE oldgirl DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
# 默认创建相当于指定如下字符集
CREATE DATABASE oldboy; # 与下面是等价的(8.0.26)
CREATE DATABASE oldgirl DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
# 问：字符集和校对规则去哪查？
show charset;
```
```
#创建一个名为HA的数据库
mysql> create database HA;
Query OK, 1 row affected (0.05 sec)
mysql> create database `HA-test`;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| ha |
| ha-test |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
show databases like 'HA'; #<==匹配HA字符串的内容。
show databases like 'HA%'; #<==%为通配符，表示匹配以HA开头的所有内容。
```
1.字符集和校对规则
`show charset;` #查支持的字符集和校对规则
`show collation;` #查支持的校对规则
2.有关字符集说明
8.0默认字符集是utf8mb4，是当前主流字符集，适合英文\中文,PC,移动端多场景。utf8mb4大于utf8(utf8mb3)，类似ext4大于ext3
重点知识：
通过命令查出的建库语句结尾多了一些东西，其中`DEFAULT CHARACTER SET utf8mb4`，是数据库默认的字符集8.0之后是utf8mb4，8.0之前是latin1，查询字符集的命令为：`show charset;`。
`COLLATE utf8mb4_0900_ai_ci`为校对规则，查询校对规则的命令为：`show collation;` 字符集的不一致会导致数据库中文内容出现乱码，后文章节会详细讲解字符集和校对规则，此处完全默认即可。
#查看数据库存放目录
```
ls `cat /etc/my.cnf|grep innodb_data_home_dir|awk -F= '{print $2}'`
auto.cnf client-cert.pem ha@002dtest ib_buffer_pool ib_logfile1 performance_schema
ca-key.pem client-key.pem mysql-bin.000023 mysql.ibd sys
ca.pem ha mysql-bin.000022 mysql mysql-bin.000024 mysql-slow.log
```
使用USE语句将会选择一个数据库成为当前数据库。后面的操作默认都在被选择的数据库中操作。
```
**选择数据库**
mysql> use HA-test;
Database changed
#查看自己所处的位置及默认所在的位置
mysql> select database();
+------------+
| database() |
+------------+
| ha-test |
+------------+
1 row in set (0.00 sec)
# 默认 Null意味着没有选择数据库
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
```
Null在数据库中表示 不知道的数据，主要有3种意思：
1）知道数据存在，但不知道具体值.
2）不知道数据是否存在.
3）数据不存在.
**在命令行选择默认的数据库**
```
[root@jkl1234 ~]# mysql -uroot -proot HA
mysql> select now(),user(),database();
+---------------------+----------------+------------+
| now() | user() | database() |
+---------------------+----------------+------------+
| 2022-06-11 14:59:03 | root@localhost | ha |
+---------------------+----------------+------------+
1 row in set (0.00 sec)
mysql>
```
**修改数据库**
```
#语法:
ALTER DATABASE [db_name]
CHARACTER SET = charset_name
COLLATE = collation_name;
#例子：修改字符集为utf8
alter database oldboy charset utf8;
```
**删除数据库：**
方法1:
```
mysql> drop database `HA-test`;
```
删除没有任何提示，要慎重操作
方法2：直接到数据库存放目录移出就行
```
[root@jkl1234 ~]# cat /etc/my.cnf|grep innodb_data_home_dir
innodb_data_home_dir = /www/server/data
[root@jkl1234 ~]# cd /www/server/data
[root@jkl1234 ~]# mv HA@002dtest /tmp
mysql> show databases;
```
使用IF EXISTS 子句以避免删除不存在的数据库时出现的MySQL错误信息
```
mysql> drop database `HA-test`;
Query OK, 0 rows affected (0.01 sec)
mysql> drop database `HA-test`;
ERROR 1008 (HY000): Can't drop database 'ha-test'; database doesn't exist
mysql> drop database if exists `HA-test`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
```
**IF EXISTS：如果存在**
同理我们创建数据库时也可以使用
```
mysql> create database ha;
ERROR 1007 (HY000): Can't create database 'ha'; database exists
mysql> create database if not exists ha;
Query OK, 1 row affected, 1 warning (0.00 sec)
```
**管理库生产经验**
1.管理员谨慎使用drop命令。
2.业务用户中，禁止有drop权限。
3.数据库名不能用大写字母,不能是关键字，不能使数字开头。
4.创建数据库时应显示设置字符集。
`CREATE DATABASE oldboy CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;`
---
## DDL语句之管理表
### 1.表数据类型
表的数据类型作用于列上的，用于控制存储数据的"格式"和规范。
**a.整型**
**b.浮点型（小数）**
除了整数之外，还有浮点数，一般用于对于精度要求很高的业务，例如和钱有关的业务，例如：99.99。
可以使用放大倍数手法把浮点数变成整数存储，例如：99.99放大100倍，为9999，使用在缩小100倍。
提示：在数据库里以整数形式存储,提高读取效率.
面试：
1.请描述tinyint、int、 bigint区别？ 解答：见表。
2.你们公司怎么存储浮点数数据的?
解答：金钱(精度要求高)有关的decimal，精度要求不高的，统一放大10的n次方倍，变成整型存储。
**c.字符类型**
char(10) 定长，浪费存储空间，作为条件列查询会更快。
varchar(10) 变长，节省空间
面试题：char(10) 和 varchar(10) 区别?
1.相同点：都是字符串类型，最多都只能存10个字符。
2.不同点: char 定长 浪费存储空间 作为条件列查询会更快(不要计算,直接读取内容相对多),varchar 变长,节省空间.
**d.时间类型**
时间类型往往是数据表中的必备类型
问题1:字符串类型可以放数字么?
可以，还是字符串类型。
问题2:那要数字类型还有啥用?
有用. 1.数字可以用来计算的，而字符串不行. 2.数字查询效率更高.
**e.枚举类型**
判断，选择：enum('N','Y')
性别：enum('F','M','N')
省份：固定多个简单值的内容。
### 2.表的约束属性
生产经验：
1.每张表务必设置一个主键列，建议是数字且自增，学号，身份证，订单编号。
2.表的每个列尽量设置非空，给默认值。
### 3.表的其它属性
### 4.建表
1)建库
```
drop database if exists oldboy;
create database oldboy;
use oldboy;
```
2)在oldboy库创建表stu
```
create table <表名> (
<字段名1> <类型1>,
...
<字段名n> <类型n>
);
```
下面是创建stu表的完整语句示例，表名为stu。
```
USE oldboy;
CREATE TABLE stu(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(64) NOT NULL COMMENT '姓名',
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
gender ENUM('M','F','N') NOT NULL DEFAULT 'N' COMMENT '性别',
telnum CHAR(15) NOT NULL DEFAULT '0' COMMENT '手机号'
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表';
#修饰-----
primary key 主键
unique 唯一
auto_increment 自动增长
default "" 默认值
null 是否允许NULL，不允许null （NOT NULL）
comment 注释
```
### 5.查看表信息
```
# 查看表信息
show tables;或show tables from oldboy;
# 查看表结构
desc stu;
```
### 6.更改表名
```
rename table stu to test;
#或
alter table test rename to stu;
```
### 7.增删改表字段
```
（1）添加列默认到所有列结尾，命令语法为：
alter table 表名 add 字段 类型 其他;
#1.在stu表中添加addr列，默认是最后一列
ALTER TABLE stu ADD addr VARCHAR(100) NOT NULL COMMENT '地址';
#2.在sname列后插入dept列。使用after sname;
alter table stu add dept varchar(32) after sname;
#3.在首列插入qq列。使用first
alter table stu add qq varchar(15) first;
#4.若要同时添加以上两个字段，还可采用如下命令
alter table stu drop dept;
alter table stu drop qq;
alter table stu add dept varchar(32) first,add qq varchar(15);
#5.修改字段类型的命令如下：
alter table stu modify dept char(64); #<==将dept数据类型及长度改为char(64)。
#6.删除列的命令如下
alter table stu drop qq;
alter table stu drop dept;
alter table stu drop addr;
```
### 8.删除表
drop table stu;
### 回顾关于表的操作
创建表：
语法：create table 表名 (字段名 类型, 字段名 类型, 字段名 类型);
`mysql> create table student(id int(20),name char(40),age int);`
**查看表相关信息：**
要进入到数据库再查看
```
# 查看表：
mysql> show tables;
+--------------+
| Tables_in_ha |
+--------------+
| student |
+--------------+
1 row in set (0.00 sec)
# 查看表的结构：
mysql> desc student;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | char(40) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> explain mysql.user;
mysql> show columns from mysql.user;
mysql> show fields from mysql.user;
mysql> show columns from mysql.user like '%user';
#会一种常用的就行
mysql> desc table student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
```
**查看创建表执行了哪些命令：**
```
mysql> show create table student \G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int DEFAULT NULL,
`name` char(40) COLLATE utf8mb4_general_ci DEFAULT NULL,
`age` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
# 可以指定表的默认存储引擎和字符集
mysql> create table student2(id int(20),name char(40),age int)ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> show create table student2 \G
*************************** 1. row ***************************
Table: student2
Create Table: CREATE TABLE `student2` (
`id` int DEFAULT NULL,
`name` char(40) DEFAULT NULL,
`age` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)
```
这两个是默认存储引擎和默认字符集
**删除表：**
`mysql> drop table student2;`
**修改表名称alter：**
语法：alter table 表名 rename 新表名;
```
mysql> alter table student rename students; #studen表名修改为students
mysql> show tables;
+--------------+
| Tables_in_HA |
+--------------+
| students |
+--------------+
```
**修改表中的字段类型：**
语法：`alter table 表名 modify 要修改的字段名 要修改的类型;`
```
mysql> desc students;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | char(40) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table students modify id char(10);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | char(10) | YES | | NULL | |
| name | char(40) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
```
**修改表中的字段类型和字段名称：**
`语法：alter table 表名 change 原字段名 新字段名 新字段类型;`
```
mysql> desc students;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | char(10) | YES | | NULL | |
| name | char(40) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table students change name stname char(20);
mysql> desc students;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id | char(10) | YES | | NULL | |
| stname | char(20) | YES | | NULL | |
| age | int | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
```
注：CHANGE 和MODIFY的区别：
CHANGE 对列进行重命名和更改列的类型，需给定旧的列名称和新的列名称、当前的类型。 MODIFY 可以改变列的类型，此时不需要重命名（不需给定新的列名称）
**在表中添加字段：**
语法：`alter table 表名 add 字段名 字段类型；`
```
mysql> alter table students add sex enum('M','W');
mysql> desc students;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | char(10) | YES | | NULL | |
| stname | char(20) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | enum('M','W') | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
```
**指定位置添加字段：**
在第一列添加一个字段：
```
mysql> alter table students add uid int(10) first;
mysql> desc students;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| uid | int | YES | | NULL | |
| id | char(10) | YES | | NULL | |
| stname | char(20) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | enum('M','W') | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
```
在age后面添加一个address字段：
```
mysql> alter table students add address char(40) after age;
mysql> desc students;
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| uid | int | YES | | NULL | |
| id | char(10) | YES | | NULL | |
| stname | char(20) | YES | | NULL | |
| age | int | YES | | NULL | |
| address | char(40) | YES | | NULL | |
| sex | enum('M','W') | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
```
**删除表中字段：**
语法：`alter table 表名 drop 字段名 ;`
```
mysql> alter table students drop address;
mysql> desc students;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| uid | int | YES | | NULL | |
| id | char(10) | YES | | NULL | |
| stname | char(20) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | enum('M','W') | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
```