1、DDL 数据库模式定义语言 (常用)
MariaDB [mysql_test]> desc test_table; //查看表结构
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| age | varchar(20) | YES | | NULL | |
| score | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
usage: ALTER TABLE 表名 <ADD/DROP/MODIFY> 列名 字符类型(char、varchar、int) //修改表结构
例:
MariaDB [mysql_test]> alter table test_table add id int; //添加一个字段 列名为id,类型为int
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [mysql_test]> desc test_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| age | varchar(20) | YES | | NULL | |
| score | varchar(20) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
添加多个字段:
MariaDB [mysql_test]> desc test_table;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
MariaDB [mysql_test]> alter table test_table add (name char(10),age char(20),score varchar(20));
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [mysql_test]> desc test_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| age | char(20) | YES | | NULL | |
| score | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
删除字段:
MariaDB [mysql_test]> desc test_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| age | char(20) | YES | | NULL | |
| score | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
MariaDB [mysql_test]> alter table test_table drop score; //删除字段名为score的字段
Query OK, 0 rows affected (0.31 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [mysql_test]> desc test_table;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| age | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
修改字段:
MariaDB [mysql_test]> alter table test_table modify name varchar(10);
Query OK, 0 rows affected (0.41 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [mysql_test]> desc test_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | char(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
查看创建表的详细过程:
MariaDB [mysql_test]> show create table test_table;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_table | CREATE TABLE `test_table` (
`id` int(10) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
`age` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
修改表名:
MariaDB [mysql_test]> show tables;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| test_table |
+----------------------+
1 row in set (0.00 sec)
MariaDB [mysql_test]> rename table test_table to newtable;
Query OK, 0 rows affected (0.08 sec)
MariaDB [mysql_test]> show tables;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| newtable |
+----------------------+
1 row in set (0.00 sec)
DML 对数据库中的数据进行修改,即 “增”,“删”,“改”。 [ ] 代表可选。
插入数据:usage:INSERT INTO 表名 (列名1,列名2,......) VALUES (列值1,列值2,......);
例如:
MariaDB [mysql_test]> insert into newtable (id,name,age) values (1,'zcs',18);
Query OK, 1 row affected (0.06 sec)
MariaDB [mysql_test]> select * from newtable;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | zcs | 18 |
+------+------+------+
1 row in set (0.00 sec)
更新数据:usage(用法):UPDATE [数据库名.]表名 SET 列值1=new,列值2=new2 [where 条件];
例如:
MariaDB [mysql_test]> update mysql_test.newtable set name='zs',age=18 where id='2';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
MariaDB [mysql_test]> update mysql_test.newtable set name='zs',age=18 ;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
例二:修改用户密码:
MariaDB [mysql_test]> update mysql.user set password=password('000000') where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [mysql_test]> flush privileges; //刷新数据库权限。
Query OK, 0 rows affected (0.00 sec)
删除数据:
usage1 :delete from 表名 [where 条件]; //只删除数据。
usage2 :truncat table 表名 //直接将表删除,在创建一个相同的表;
例如:
MariaDB [mysql_test]> insert into newtable (id,name,age) values (2,'zz',19),(3,'cc',19),(4,'qq',20);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [mysql_test]> select * from newtable;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | zs | 18 |
| 2 | zz | 19 |
| 3 | cc | 19 |
| 4 | qq | 20 |
+------+------+------+
4 rows in set (0.00 sec)
MariaDB [mysql_test]> delete from newtable where id='1' or name='zz';
Query OK, 2 rows affected (0.02 sec)
MariaDB [mysql_test]> select * from newtable;
+------+------+------+
| id | name | age |
+------+------+------+
| 3 | cc | 19 |
| 4 | qq | 20 |
+------+------+------+
2 rows in set (0.00 sec)
删除所有数据:
MariaDB [mysql_test]> delete from newtable;
通配符:
= | 等于 |
---|---|
!= | 不等于 |
<> | 不等于 |
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
- | 匹配一个字符 |
% | 匹配多个字符 |
MariaDB [mysql_test]> show tables;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| test |
+----------------------+
1 row in set (0.00 sec)
MariaDB [mysql_test]> select * from test; 查询test表中所有的数据;
+----+------+--------+-------+-----------+--------+
| id | name | 学号 | score | 职业 | 地址 |
+----+------+--------+-------+-----------+--------+
| 1 | a | 111 | 51 | 云计算 | 重庆 |
| 2 | b | 222 | 56 | 云计算 | 重庆 |
| 3 | x | 33 | 89 | 云计算 | 重庆 |
| 4 | d | 444 | 54 | 云计算 | 重庆 |
| 5 | e | 555 | 78 | 云计算 | 重庆 |
| 6 | f | 666 | 6 | 云计算 | 重庆 |
| 7 | g | 777 | 68 | 云计算 | 重庆 |
| 8 | h | 888 | 19 | 云计算 | 重庆 |
| 9 | j | 999 | 56 | 云计算 | 重庆 |
| 10 | i | 101 | 36 | 云计算 | 重庆 |
| 11 | k | 102 | 38 | 云计算 | 重庆 |
| 12 | l | 103 | 78 | 云计算 | 重庆 |
| 13 | q | 104 | 69 | 云计算 | 重庆 |
| 14 | w | 105 | 90 | 云计算 | 重庆 |
+----+------+--------+-------+-----------+--------+
14 rows in set (0.00 sec)
MariaDB [mysql_test]> select * from test where id=1 and name='a'; 添加条件查询
+----+------+--------+-------+-----------+--------+
| id | name | 学号 | score | 职业 | 地址 |
+----+------+--------+-------+-----------+--------+
| 1 | a | 111 | 51 | 云计算 | 重庆 |
+----+------+--------+-------+-----------+--------+
MariaDB [mysql_test]> select * from test where id!=1 and name!='a'; and用法
+----+------+--------+-------+-----------+--------+
| id | name | 学号 | score | 职业 | 地址 |
+----+------+--------+-------+-----------+--------+
| 2 | b | 222 | 56 | 云计算 | 重庆 |
| 3 | x | 33 | 89 | 云计算 | 重庆 |
| 4 | d | 444 | 54 | 云计算 | 重庆 |
| 5 | e | 555 | 78 | 云计算 | 重庆 |
| 6 | f | 666 | 6 | 云计算 | 重庆 |
| 7 | g | 777 | 68 | 云计算 | 重庆 |
| 8 | h | 888 | 19 | 云计算 | 重庆 |
| 9 | j | 999 | 56 | 云计算 | 重庆 |
| 10 | i | 101 | 36 | 云计算 | 重庆 |
| 11 | k | 102 | 38 | 云计算 | 重庆 |
| 12 | l | 103 | 78 | 云计算 | 重庆 |
| 13 | q | 104 | 69 | 云计算 | 重庆 |
| 14 | w | 105 | 90 | 云计算 | 重庆 |
+----+------+--------+-------+-----------+--------+
13 rows in set (0.00 sec)
MariaDB [mysql_test]> select * from test where id=1 or name='f'; or用法
+----+------+--------+-------+-----------+--------+
| id | name | 学号 | score | 职业 | 地址 |
+----+------+--------+-------+-----------+--------+
| 1 | a | 111 | 51 | 云计算 | 重庆 |
| 6 | f | 666 | 6 | 云计算 | 重庆 |
+----+------+--------+-------+-----------+--------+
2 rows in set (0.00 sec)
MariaDB [mysql_test]> select * from test where not id=1; not用法
+----+------+--------+-------+-----------+--------+
| id | name | 学号 | score | 职业 | 地址 |
+----+------+--------+-------+-----------+--------+
| 2 | b | 222 | 56 | 云计算 | 重庆 |
| 3 | x | 33 | 89 | 云计算 | 重庆 |
| 4 | d | 444 | 54 | 云计算 | 重庆 |
| 5 | e | 555 | 78 | 云计算 | 重庆 |
| 6 | f | 666 | 6 | 云计算 | 重庆 |
| 7 | g | 777 | 68 | 云计算 | 重庆 |
| 8 | h | 888 | 19 | 云计算 | 重庆 |
| 9 | j | 999 | 56 | 云计算 | 重庆 |
| 10 | i | 101 | 36 | 云计算 | 重庆 |
| 11 | k | 102 | 38 | 云计算 | 重庆 |
| 12 | l | 103 | 78 | 云计算 | 重庆 |
| 13 | q | 104 | 69 | 云计算 | 重庆 |
| 14 | w | 105 | 90 | 云计算 | 重庆 |
+----+------+--------+-------+-----------+--------+
13 rows in set (0.00 sec)
MariaDB [mysql_test]> SELECT * FROM mysql_test.test WHERE id BETWEEN 2 AND 5;
BETWEEN...AND...用法。
+----+------+--------+-------+-----------+--------+
| id | name | 学号 | score | 职业 | 地址 |
+----+------+--------+-------+-----------+--------+
| 2 | b | 222 | 56 | 云计算 | 重庆 |
| 3 | x | 33 | 89 | 云计算 | 重庆 |
| 4 | d | 444 | 54 | 云计算 | 重庆 |
| 5 | e | 555 | 78 | 云计算 | 重庆 |
+----+------+--------+-------+-----------+--------+
4 rows in set (0.00 sec)
MariaDB [mysql_test]> SELECT * FROM mysql_test.test WHERE id in(1,2,3); id用法
+----+------+--------+-------+-----------+--------+
| id | name | 学号 | score | 职业 | 地址 |
+----+------+--------+-------+-----------+--------+
| 1 | a | 111 | 51 | 云计算 | 重庆 |
| 2 | b | 222 | 56 | 云计算 | 重庆 |
| 3 | x | 33 | 89 | 云计算 | 重庆 |
+----+------+--------+-------+-----------+--------+
3 rows in set (0.00 sec)
MariaDB [mysql_test]> SELECT * FROM mysql_test.test WHERE id is null; is null用法
Empty set (0.01 sec)
模糊查询:
MariaDB [mysql_test]> SELECT * FROM mysql_test.test WHERE 学号 LIKE '_2%'; 查询符合第二个字符为数字‘2’的数据。
MariaDB [mysql_test]> SELECT *,id+score FROM test; //将id+socre的值显示出来;
+----+------+--------+-------+-----------+--------+----------+
| id | name | 学号 | score | 职业 | 地址 | id+score |
+----+------+--------+-------+-----------+--------+----------+
| 1 | a | 111 | 51 | 云计算 | 重庆 | 52 |
| 2 | b | 222 | 56 | 云计算 | 重庆 | 58 |
| 3 | x | 33 | 89 | 云计算 | 重庆 | 92 |
| 4 | d | 444 | 54 | 云计算 | 重庆 | 58 |
| 5 | e | 555 | 78 | 云计算 | 重庆 | 83 |
| 6 | f | 666 | 6 | 云计算 | 重庆 | 12 |
| 7 | g | 777 | 68 | 云计算 | 重庆 | 75 |
| 8 | h | 888 | 19 | 云计算 | 重庆 | 27 |
| 9 | j | 999 | 56 | 云计算 | 重庆 | 65 |
| 10 | i | 101 | 36 | 云计算 | 重庆 | 46 |
| 11 | k | 102 | 38 | 云计算 | 重庆 | 49 |
| 12 | l | 103 | 78 | 云计算 | 重庆 | 90 |
| 13 | q | 104 | 69 | 云计算 | 重庆 | 82 |
| 14 | w | 105 | 90 | 云计算 | 重庆 | 104 |
+----+------+--------+-------+-----------+--------+----------+
14 rows in set (0.01 sec)
MariaDB [mysql_test]> SELECT *,id+score AS total FROM test; 使用AS 定义别名total。
+----+------+--------+-------+-----------+--------+-------+
| id | name | 学号 | score | 职业 | 地址 | total |
+----+------+--------+-------+-----------+--------+-------+
| 1 | a | 111 | 51 | 云计算 | 重庆 | 52 |
| 2 | b | 222 | 56 | 云计算 | 重庆 | 58 |
| 3 | x | 33 | 89 | 云计算 | 重庆 | 92 |
| 4 | d | 444 | 54 | 云计算 | 重庆 | 58 |
| 5 | e | 555 | 78 | 云计算 | 重庆 | 83 |
| 6 | f | 666 | 6 | 云计算 | 重庆 | 12 |
| 7 | g | 777 | 68 | 云计算 | 重庆 | 75 |
| 8 | h | 888 | 19 | 云计算 | 重庆 | 27 |
| 9 | j | 999 | 56 | 云计算 | 重庆 | 65 |
| 10 | i | 101 | 36 | 云计算 | 重庆 | 46 |
| 11 | k | 102 | 38 | 云计算 | 重庆 | 49 |
| 12 | l | 103 | 78 | 云计算 | 重庆 | 90 |
| 13 | q | 104 | 69 | 云计算 | 重庆 | 82 |
| 14 | w | 105 | 90 | 云计算 | 重庆 | 104 |
+----+------+--------+-------+-----------+--------+-------+
14 rows in set (0.00 sec)
MariaDB [mysql_test]> SELECT *,IFNULL(id,0)+IFNULL(score,0) AS total FROM test;
//IFNULL(id,0)如果id为null,将id的值定义为0,
+----+------+--------+-------+-----------+--------+-------+
| id | name | 学号 | score | 职业 | 地址 | total |
+----+------+--------+-------+-----------+--------+-------+
| 1 | a | 111 | 51 | 云计算 | 重庆 | 52 |
| 2 | b | 222 | 56 | 云计算 | 重庆 | 58 |
| 3 | x | 33 | 89 | 云计算 | 重庆 | 92 |
| 4 | d | 444 | 54 | 云计算 | 重庆 | 58 |
| 5 | e | 555 | 78 | 云计算 | 重庆 | 83 |
| 6 | f | 666 | 6 | 云计算 | 重庆 | 12 |
| 7 | g | 777 | 68 | 云计算 | 重庆 | 75 |
| 8 | h | 888 | 19 | 云计算 | 重庆 | 27 |
| 9 | j | 999 | 56 | 云计算 | 重庆 | 65 |
| 10 | i | 101 | 36 | 云计算 | 重庆 | 46 |
| 11 | k | 102 | 38 | 云计算 | 重庆 | 49 |
| 12 | l | 103 | 78 | 云计算 | 重庆 | 90 |
| 13 | q | 104 | 69 | 云计算 | 重庆 | 82 |
| 14 | w | 105 | 90 | 云计算 | 重庆 | 104 |
| 15 | aa | NULL | NULL | 云计算 | 重庆 | 15 |
| 16 | bb | NULL | NULL | 云计算 | 重庆 | 16 |
+----+------+--------+-------+-----------+--------+-------+
16 rows in set (0.00 sec)
函数:SUM,AVG,COUNT,MAX,MIN,IFNULL,GROUP_CONCAT配合GROUP BY。
计数每个部门的人数
MariaDB [mysql_test]> SELECT 部门,COUNT(name),GROUP_CONCAT(name) FROM test1 GROUP BY 部门;
+-----------+-------------+-------------------------+
| 部门 | COUNT(name) | GROUP_CONCAT(name) |
+-----------+-------------+-------------------------+
| 人事部 | 3 | 王五,吴十,郑一 |
| 技术部 | 4 | 张三,赵六,钱七,aa |
| 财务部 | 2 | 李四,周九 |
+-----------+-------------+-------------------------+
显示所有的数据,并显示工资的计数
MariaDB [mysql_test]> SELECT *,COUNT(工资) FROM test1;
+------+--------+--------+--------+--------+-----------+---------------+
| id | name | 绩效 | 工资 | 奖金 | 部门 | COUNT(工资) |
+------+--------+--------+--------+--------+-----------+---------------+
| 1 | 张三 | 1000 | 4000 | 1000 | 技术部 | 9 |
+------+--------+--------+--------+--------+-----------+---------------+
计数工资大于5000的条目
MariaDB [mysql_test]> SELECT COUNT(*) FROM test1 where 工资 > 5000;"
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
显示奖金+工资大于5000的条目。
MariaDB [mysql_test]> SELECT * FROM test1 WHERE IFNULL(奖金,0) + 工资 > 5000;
+----+--------+--------+--------+--------+-----------+
| id | name | 绩效 | 工资 | 奖金 | 部门 |
+----+--------+--------+--------+--------+-----------+
| 4 | 赵六 | 2000 | 8000 | 1000 | 技术部 |
| 5 | 钱七 | 1780 | 7500 | 700 | 技术部 |
| 6 | aa | 156 | 5656 | NULL | 技术部 |
| 8 | 吴十 | 5000 | 30000 | 1500 | 人事部 |
+----+--------+--------+--------+--------+-----------+
MariaDB [mysql_test]> SELECT COUNT(name),COUNT( 工资) FROM test1;"
+-------------+---------------+
| COUNT(name) | COUNT(工资) |
+-------------+---------------+
| 9 | 9 |
+-------------+---------------+
工资求和:
MariaDB [mysql_test]> select sum(工资) from test1;"
+-------------+
| sum(工资) |
+-------------+
| 66656 |
+-------------+
工资+奖金求和:
MariaDB [mysql_test]> select sum(工资 + IFNULL( 奖金,0)) AS total from test1;"
+-------+
| total |
+-------+
| 71856 |
+-------+
求平均值:
MariaDB [mysql_test]> select avg(工资 + IFNULL( 奖金,0)) AS total from test1;"
+-------+
| total |
+-------+
| 7984 |
+-------+
求最大,最小值;
MariaDB [mysql_test]> select max(工资) AS 最高工资,min(奖 金) AS 最低工资 from test1;"
+--------------+--------------+
| 最高工资 | 最低工资 |
+--------------+--------------+
| 8000 | 1000 |
+--------------+--------------+
DCL 数据库控制语言