一、DDL（数据定义语言）
1.1 Create
Database
#创建数据库huhuhahei
mysql> create database huhuhahei;
Query OK, 1 row affected (0.00 sec)
#创建数据库并指定属性
mysql> create database huhuhahei1 charset utf8;
Query OK, 1 row affected (0.00 sec)
Table
#创建student表
CREATE TABLE `student` (
`sno` int(20) NOT NULL COMMENT '学号',
`sname` varchar(15) NOT NULL COMMENT '学生姓名',
`sage` tinyint(3) unsigned NOT NULL COMMENT '学生年龄',
`ssex` enum('男','女') NOT NULL DEFAULT '男',
`sbirthday` datetime DEFAULT CURRENT_TIMESTAMP,
`class` int(10) NOT NULL,
PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#创建course表
CREATE TABLE `course` (
`cno` int(20) NOT NULL COMMENT '课程号',
`cname` varchar(20) NOT NULL COMMENT '课程名称',
`tno` int(3) NOT NULL COMMENT '课程编号',
PRIMARY KEY (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#创建score表
CREATE TABLE `score` (
`sno` int(20) NOT NULL COMMENT '学号',
`cno` int(20) NOT NULL COMMENT '课程号',
`mark` float(4,1) NOT NULL COMMENT '成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#创建teacher表
CREATE TABLE `teacher` (
`tno` int(20) NOT NULL COMMENT '教师编号',
`tname` varchar(20) NOT NULL COMMENT '教师名称',
`tage` tinyint(3) unsigned NOT NULL COMMENT '教师年龄',
`tsex` enum('男','女') NOT NULL DEFAULT '男' COMMENT '教师性别',
`prof` varchar(20) DEFAULT 'null' COMMENT '教师职称',
`depart` varchar(20) NOT NULL COMMENT '教师部门',
PRIMARY KEY (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#查看表结构
mysql> desc score;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| sno | int(20) | NO | | NULL | |
| cno | int(20) | NO | | NULL | |
| mark | float(4,1) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
数据类型
#数据类型
int： 整数 -231 ~ 231 -1
varchar：字符类型 （变长）
char： 字符类型 （定长）
tinyint： 整数 -128 ~ 128
enum： 枚举类型
datetime： 时间类型 年月日时分秒
#数据属性
not null： 非空
primary key： 主键（唯一且非空的）
auto_increment： 自增（此列必须是：primary key或者unique key）
unique key： 单独的唯一的
default： 默认值
unsigned： 非负数
comment： 注释
1.2 Drop
Database
#删除数据库
mysql> drop database huhuhahei;
Query OK, 4 rows affected (0.01 sec)
Table
#删除表
mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)
1.3 Alter
Database
#修改数据属性
mysql> alter database huhuhahei1 charset gbk;
Query OK, 1 row affected (0.00 sec)
Table
#修改表名
mysql> alter table test rename test2;
Query OK, 0 rows affected (0.00 sec)
#添加列
mysql> alter table test2 add age int;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#添加多个列
mysql> alter table test2 add test int ,add test2 varchar(10);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#添加列到表首
mysql> alter table test2 add aaaa varchar(10) first;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#添加列到指定位置
mysql> alter table test2 add bbb int after age;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#删除列
mysql> alter table test2 drop bbb;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#修改列的属性
mysql> alter table test2 modify aaaa int;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
#修改列明和属性
mysql> alter table test2 change aaaa aaa varchar(2) primary key;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#修改数据库字符集
alter database oldboy CHARACTER SET utf8 collate utf8_general_ci;
#修改表字符集
alter table t1 CHARACTER SET utf8;
二、DCL（数据控制语言）
2.1 Grant
#创建test@127.0.0.1用户并授予所有权限（非超级管理员）
mysql> grant all on *.* to test@'127.0.0.1' identified by '123';
Query OK, 0 rows affected (0.00 sec)
#创建用户给授权为超级管理员
mysql> grant all on *.* to test@'127.0.0.1' identified by '123' with grant option;
Query OK, 0 rows affected (0.00 sec)
#授予用户单列权限
mysql> grant select(sno) on huhuhahei.score to test11@localhost identified by '123';
Query OK, 0 rows affected (0.00 sec)
#扩展权限
max_queries_per_hour：一个用户每小时可发出的查询数量
max_updates_per_hour：一个用户每小时可发出的更新数量
max_connetions_per_hour：一个用户每小时可连接到服务器的次数
max_user_connetions：允许同时连接数量
2.2 Revoke
#收回select权限
mysql> revoke select on *.* from test@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)
三、DML（数据操作语言）
3.1 Insert
#插入数据
mysql> insert into student(sno,sname,sage,class) values(1,'徐导',20,1),(2,'曾导',18,1),(3,'李导',17,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
3.2 Update
#更新数据
mysql> update student set sage='100' where sno='1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#全表更新（::(狗头)慎用）
mysql> update student set sage='100' where 1=1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3 Changed: 2 Warnings: 0
3.3 Delete
#删除指定数据
mysql> delete from test2 where id=2;
Query OK, 1 row affected (0.00 sec)
#删除表:@(吐舌)
mysql> truncate table test2;
Query OK, 0 rows affected (0.00 sec)
使用update代替delete实现伪删除
#创建状态列
mysql> alter table student add status enum('1','0') default 1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#更新目前所有列
mysql> update student set status=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3 Changed: 0 Warnings: 0
#根据状态列查询
mysql> select * from student where status=1;
+-----+--------+------+------+---------------------+-------+--------+
| sno | sname | sage | ssex | sbirthday | class | status |
+-----+--------+------+------+---------------------+-------+--------+
| 1 | 徐导 | 100 | 男 | 2021-10-31 17:19:51 | 1 | 1 |
| 2 | 曾导 | 100 | 男 | 2021-10-31 17:19:51 | 1 | 1 |
| 3 | 李导 | 100 | 男 | 2021-10-31 17:19:51 | 2 | 1 |
+-----+--------+------+------+---------------------+-------+--------+
3 rows in set (0.00 sec)
四、DQL（数据查询语句）
4.1 Select
#简单查询
mysql> select countrycode,name from city;
#行级查询
mysql> select countrycode,name from city limit 2;
mysql> select countrycode,name from city limit 2,2;
#条件查询
mysql> select id,population,district from city where countrycode='CHN';
#多条件查询
mysql> select id,population from city where countrycode='CHN' and district='Shandong';
#模糊查询
mysql> select name,population from city where countrycode='CHN' and district='heilongjiang';
#排序查询
mysql> select id,name,population,countrycode from city order by countrycode limit 10;
#倒序查询
mysql> select id,name,population,countrycode from city order by countrycode desc limit 10;
#范围查询
mysql> select * from city where population>=1410000;
#OR语句使用
mysql> select * from city where countrycode='CHN' or countrycode='USA';
#IN语句使用
mysql> select * from city where countrycode in ('CHN','USA');
#select union all 实现or用法
mysql> select * from city where countrycode='CHN'
-> union all
-> select * from city where countrycode='USA';
#union 实现in用法
mysql> select * from city where countrycode='CHN'
-> union
-> select * from city where countrycode='USA';
#查询表的行数
select count(*) from world.city;
#去重
select count(distinct countrycode) from world.city;
Select高级用法
#传统连接查询
mysql> select country.name,countrylanguage.language,city.name,country.population
-> from city,country,countrylanguage
-> where countrylanguage.countrycode=country.code
-> and city.countrycode=country.code
-> and country.population<100;
+----------+-------------+-----------+------------+
| name | language | name | population |
+----------+-------------+-----------+------------+
| Pitcairn | Pitcairnese | Adamstown | 50 |
+----------+-------------+-----------+------------+
1 row in set (0.00 sec)
#natural join查询（自连接的表中有共同的列名称）
mysql> select city.name,countrylanguage.language,city.countrycode
-> from city natural join countrylanguage
-> where population = 2352121;
+-----------+------------+-------------+
| name | language | countrycode |
+-----------+------------+-------------+
| Cape Town | Afrikaans | ZAF |
| Cape Town | English | ZAF |
| Cape Town | Ndebele | ZAF |
| Cape Town | Northsotho | ZAF |
| Cape Town | Southsotho | ZAF |
| Cape Town | Swazi | ZAF |
| Cape Town | Tsonga | ZAF |
| Cape Town | Tswana | ZAF |
| Cape Town | Venda | ZAF |
| Cape Town | Xhosa | ZAF |
| Cape Town | Zulu | ZAF |
+-----------+------------+-------------+
11 rows in set (0.01 sec)
#内连接join on（小表驱动大表）
mysql> select city.name,city.countrycode,country.name
-> from city join country
-> on city.countrycode=country.code
-> where city.population<100;
+-----------+-------------+----------+
| name | countrycode | name |
+-----------+-------------+----------+
| Adamstown | PCN | Pitcairn |
+-----------+-------------+----------+
1 row in set (0.01 sec)
#外连接（左）
mysql> select city.name,city.countrycode,country.name
-> from city left join country
-> on city.countrycode=country.code
-> and city.population<100;
外连接（右）
mysql> select city.name,city.countrycode,country.name
-> from city right join country
-> on city.countrycode=country.code
-> and city.population<100
-> limit 10;
4.2 Show
#查看所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| blog |
| huhuhahei |
| mysql |
| performance_schema |
| test |
| wordpress |
+--------------------+
7 rows in set (0.00 sec)
#查看数据库创建sql
mysql> show create database huhuhahei;
+-----------+--------------------------------------------------------------------+
| Database | Create Database |
+-----------+--------------------------------------------------------------------+
| huhuhahei | CREATE DATABASE `huhuhahei` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
#查看数据表
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)
#查看创表语句
mysql> show create table score;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| score | CREATE TABLE `score` (
`sno` int(20) NOT NULL COMMENT '学号',
`cno` int(20) NOT NULL COMMENT '课程号',
`mark` float(4,1) NOT NULL COMMENT '成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec
#查看现有权限
mysql> show grants for test@'127.0.0.1';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@127.0.0.1 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'test'@'127.0.0.1' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#查看字符集
mysql> show charset;
#查看校验规则
mysql> show collation;
#查看db最大连接数；
mysql> show variables like '%connections%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| max_connections | 151 |
| max_user_connections | 0 |
+----------------------+-------+
2 rows in set (0.00 sec)
#查看事务隔离级别
mysql> show variables like "%iso%";
+---------------+------------------+
| Variable_name | Value |
+---------------+------------------+
| tx_isolation | READ-UNCOMMITTED |
+---------------+------------------+
1 row in set (0.00 sec)
转载 drz
