SQL语句分类
DDL: Data Defination Language 数据定义语言
CREATE，DROP，ALTER
DML: Data Manipulation Language 数据操纵语言
INSERT，DELETE，UPDATE
DQL：Data Query Language 数据查询语言
SELECT
DCL：Data Control Language 数据控制语言
GRANT，REVOKE，COMMIT，ROLLBACK
创建数据库文件
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> create database if not exists db2 character set 'utf8';
Query OK, 1 row affected (0.00 sec)
修改数据库
mysql> show create database db1;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter database db1 character set 'utf8';
Query OK, 1 row affected (0.00 sec)
mysql> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
删除数据库
mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db2 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
创建表
创建一个id为主键且不为负值自增长，学生名字不为空，年龄不为负值，性别选择（男，女）默认男，数据库引擎为innodb，从10开始递增，默认字符集为UTF-8的学生表
mysql> create table student ( id int unsigned auto_increment primary key, name varchar(20) not null, age tinyint unsigned, gender enum('M','F') default 'M' ) engine=innodb auto_increment=10 default charset='utf8';
Query OK, 0 rows affected (0.21 sec)
mysql> show create table student;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
`gender` enum('M','F') DEFAULT 'M',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.22 sec)
mysql> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> show variables like 'auto_in%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> create table date ( id int unsigned auto_increment primary key, date timestamp default current_timestamp not null);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into date (id) values (3);
Query OK, 1 row affected (0.01 sec)
mysql> select * from date;
+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2020-12-23 10:10:31 |
| 2 | 2020-12-23 10:10:34 |
| 3 | 2020-12-23 10:10:36 |
+----+---------------------+
3 rows in set (0.00 sec)
双主键
mysql> CREATE TABLE employee (id int UNSIGNED NOT NULL ,name VARCHAR(20) NOT NULL,age
-> tinyint UNSIGNED,PRIMARY KEY(id,name));
Query OK, 0 rows affected (0.00 sec)
mysql> desc employee;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(20) | NO | PRI | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
查看表
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| date |
| student |
+---------------+
2 rows in set (0.00 sec)
mysql> show columns from student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
查看表状态
mysql> show table status like student;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'student' at line 1
mysql> show table status like 'student';
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| student | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | 10 | 2020-12-23 09:49:13 | NULL | NULL | utf8_general_ci | NULL | | |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)
mysql> show table status like 'student'\G;
*************************** 1. row ***************************
Name: student
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 10
Create_time: 2020-12-23 09:49:13
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR:
No query specified
修改表
ALTER TABLE students RENAME s1;
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
ALTER TABLE s1 MODIFY phone int;
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
ALTER TABLE s1 DROP COLUMN mobile;
ALTER TABLE s1 character set utf8;
ALTER TABLE s1 change name name varchar(20) character set utf8;
ALTER TABLE students ADD gender ENUM('m','f');
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
ALTER TABLE students DROP age;
DESC students;
表查询
DESC students;
INSERT INTO students VALUES(1,'tom'，'m'),(2,'alice','f');
INSERT INTO students(id,name) VALUES(3,'jack'),(4,'allen');
SELECT * FROM students WHERE id < 3;
SELECT * FROM students WHERE gender='m';
SELECT * FROM students WHERE gender IS NULL;
SELECT * FROM students WHERE gender IS NOT NULL;
SELECT * FROM students ORDER BY name DESC LIMIT 2;
SELECT * FROM students ORDER BY name DESC LIMIT 1,2;
SELECT * FROM students WHERE id >=2 and id <=4
SELECT * FROM students WHERE BETWEEN 2 AND 4
SELECT * FROM students WHERE name LIKE ‘t%’
SELECT * FROM students WHERE name RLIKE '.*[lo].*';
SELECT id stuid,name as stuname FROM students
select * from students where classid in (1,3,5);
select * from students where classid not in (1,3,5)
去重
MariaDB [hellodb]> select distinct gender from students ;
+--------+
| gender |
+--------+
| M |
| F |
+--------+
2 rows in set (0.001 sec)
#分组统计
select classid,avg(age) as 平均年龄 from students where classid > 3 group by
classid having 平均年龄 >30 ;
#对classid 正序排序，NULL记录排在最后
select * from students order by -classid desc ;
#必须先过滤，再排序
select * from students where classid is not null order by gender desc, age asc ;
#多列排序
select * from students order by gender desc, age asc;
多表查询
子查询：在SQL语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询
联合查询：UNION
交叉连接：笛卡尔乘积
内连接：
等值连接：让表之间的字段以“等值”建立连接关系
不等值连接
自然连接：去掉重复列的等值连接
外连接：
左外连接：FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
右外连接：FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
自连接：本表和本表进行连接查询
联合查询：UNION
SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;
交叉连接，即笛卡尔乘积
#横向合并，交叉连接（横向笛卡尔）
MariaDB [hellodb]> select * from students cross join teachers;
MariaDB [hellodb]> select * from teachers , students;
+-----+---------------+-----+--------+-------+---------------+-----+--------+---
------+-----------+
| TID | Name | Age | Gender | StuID | Name | Age | Gender |
ClassID | TeacherID |
+-----+---------------+-----+--------+-------+---------------+-----+--------+---
------+-----------+
| 1 | Song Jiang | 45 | M | 1 | Shi Zhongyu | 22 | M |
2 | 3 |
| 2 | Zhang Sanfeng | 94 | M | 1 | Shi Zhongyu | 22 | M |
2 | 3 |
| 3 | Miejue Shitai | 77 | F | 1 | Shi Zhongyu | 22 | M |
2 | 3 |
| 4 | Lin Chaoying | 26 | F | 1 | Shi Zhongyu | 22 | M |
2 | 3 |
| 1 | Song Jiang | 45 | M | 2 | Shi Potian | 22 | M |
1 | 7 |
| 2 | Zhang Sanfeng | 94 | M | 2 | Shi Potian | 22 | M |
1 | 7 |
| 3 | Miejue Shitai | 77 | F | 2 | Shi Potian | 22 | M |
#内连接inner join
如果表定义了别名，原表名将无法使用
MariaDB [hellodb]> select stuid,s.name as student_name ,tid,t.name as
teacher_name from students as s inner join teachers as t on s.teacherid=t.tid;
+-------+--------------+-----+---------------+
| stuid | student_name | tid | teacher_name |
+-------+--------------+-----+---------------+
| 5 | Yu Yutong | 1 | Song Jiang |
| 1 | Shi Zhongyu | 3 | Miejue Shitai |
| 4 | Ding Dian | 4 | Lin Chaoying |
+-------+--------------+-----+---------------+
#左外连接
MariaDB [hellodb]> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age
from students as s left outer join teachers as t on s.teacherid=t.tid;
#右外连接的扩展用法
MariaDB [hellodb]> select * from students s right outer join teachers t on
s.teacherid=t.tid where s.teacherid is null;
#完全外连接 full outer joion
MariaDB [hellodb]> select s.stuid,s.name,s.age,t.tid,t.name,t.age from students
as s left join teachers as t on s.teacherid=t.tid
-> union
-> select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s right
join teachers as t on s.teacherid=t.tid;
扫一扫
1018
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
