[TOC]
# Mysql基本操作
## 增
### 创建数据库
1. 外部命令行
```
mysqladmin -uroot -p123456 create dbname
```
mysqladmin创建的数据库不能指定字符集
2. 内部命令行
```
CREATE DATABASE newdb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
```
3. 脚本应用
```
CREATE DATABASE IF NOT EXISTS newdb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
```
### 创建数据表
1. 创建数据表
```
create table student
(
ids int auto_increment primary key,
name varchar(20),
chinese float,
english float,
math float
);
```
### 插入数据
```
insert into student values(1,'李明',89,78,90);
insert into student values(2,'乘风',67,89,56);
insert into student values(3,'南宫流云',87,78,77);
insert into student values(4,'南宫皓月',88,98,90);
insert into student values(5,'南宫紫月',82,84,67);
insert into student values(6,'萧炎',55,85,45);
insert into student values(7,'林动',75,65,30);
```
### 插入新的一列
为student表新增一列physical类型为float
```
ALTER TABLE student ADD physical FLOAT;
```
## 删
### 删除数据库
1. 外部命令行
删除时有提示
```
mysqladmin -uroot -p drop RUNOOB
```
删除时无提示，强制删除
```
mysqladmin -uroot -p drop -f RUNOOB
```
2. 内部命令行
```
drop database RUNOOB;
```
### 删除数据表
### 删除表内数据
## 改
### 修改表名
将student表名修改为Grade
```
ALTER TABLE student RENAME TO Grade;
```
### 修改（更新）表内数据
更新林动的数学成绩为99
```
UPDATE student SET math = '99' WHERE name = '林动';
```
## 查
插入测试数据
```
create table student
(
ids int auto_increment primary key,
name varchar(20),
chinese float,
english float,
math float
);
insert into student values(1,'李明',89,78,90);
insert into student values(2,'乘风',67,89,56);
insert into student values(3,'南宫流云',87,78,77);
insert into student values(4,'南宫皓月',88,98,90);
insert into student values(5,'南宫紫月',82,84,67);
insert into student values(6,'萧炎',55,85,45);
insert into student values(7,'林动',75,65,30);
```
### 查询表内所有数据
```
mariadb> SELECT * FROM student;
+-----+----------+---------+---------+------+
| ids | name | chinese | english | math |
+-----+----------+---------+---------+------+
| 1 | 李明 | 89 | 78 | 90 |
| 2 | 乘风 | 67 | 89 | 56 |
| 3 | 南宫流云 | 87 | 78 | 77 |
| 4 | 南宫皓月 | 88 | 98 | 90 |
| 5 | 南宫紫月 | 82 | 84 | 67 |
| 6 | 萧炎 | 55 | 85 | 45 |
| 7 | 林动 | 75 | 65 | 30 |
+-----+----------+---------+---------+------+
7 rows in set
```
### 查询指定列
```
mariadb> SELECT name,math FROM student;
+----------+------+
| name | math |
+----------+------+
| 李明 | 90 |
| 乘风 | 56 |
| 南宫流云 | 77 |
| 南宫皓月 | 90 |
| 南宫紫月 | 67 |
| 萧炎 | 45 |
| 林动 | 30 |
+----------+------+
7 rows in set
```
### 去重查询
```
mariadb> SELECT DISTINCT math FROM student;
+------+
| math |
+------+
| 90 |
| 56 |
| 77 |
| 67 |
| 45 |
| 30 |
+------+
6 rows in set
```
### 查询并计算所有人的总分
```
mariadb> SELECT ids,name,(chinese+math+english) as 总分 FROM student;
+-----+----------+------+
| ids | name | 总分 |
+-----+----------+------+
| 1 | 李明 | 257 |
| 2 | 乘风 | 212 |
| 3 | 南宫流云 | 242 |
| 4 | 南宫皓月 | 276 |
| 5 | 南宫紫月 | 233 |
| 6 | 萧炎 | 185 |
| 7 | 林动 | 170 |
+-----+----------+------+
7 rows in set
```
### 查询并计算有所南宫同学的总分
```
mariadb> SELECT ids,name,(chinese+math+english) as 总分 FROM student WHERE name like "南宫%";
+-----+----------+------+
| ids | name | 总分 |
+-----+----------+------+
| 3 | 南宫流云 | 242 |
| 4 | 南宫皓月 | 276 |
| 5 | 南宫紫月 | 233 |
+-----+----------+------+
3 rows in set
```
where 子句中的运算符
**需要注意的是where 后面不能用别名，因为数据库中先执行where子句，再执行select子句。**
```
mariadb> SELECT ids,name,(chinese+math+english) as 总分 FROM student WHERE 总分 > 200;
1054 - Unknown column '总分' in 'where clause'
mariadb> SELECT ids,name,(chinese+math+english) as 总分 FROM student WHERE (chinese+math+english) > 250;
+-----+----------+------+
| ids | name | 总分 |
+-----+----------+------+
| 1 | 李明 | 257 |
| 4 | 南宫皓月 | 276 |
+-----+----------+------+
2 rows in set
```
### order by排序语句
- asc升序(默认)，desc降序
- order by 子句应该位于select语句的结尾
- order by column1 column2表示当column1相同时才会按照column2排序
1. 对数学成绩进行升序排序
```
mariadb> SELECT ids,name,math FROM student ORDER BY math;
+-----+----------+------+
| ids | name | math |
+-----+----------+------+
| 7 | 林动 | 30 |
| 6 | 萧炎 | 45 |
| 2 | 乘风 | 56 |
| 5 | 南宫紫月 | 67 |
| 3 | 南宫流云 | 77 |
| 1 | 李明 | 90 |
| 4 | 南宫皓月 | 90 |
+-----+----------+------+
7 rows in set
```
2. 对数学成绩进行降序排序
```
mariadb> SELECT ids,name,math FROM student ORDER BY math DESC;
+-----+----------+------+
| ids | name | math |
+-----+----------+------+
| 1 | 李明 | 90 |
| 4 | 南宫皓月 | 90 |
| 3 | 南宫流云 | 77 |
| 5 | 南宫紫月 | 67 |
| 2 | 乘风 | 56 |
| 6 | 萧炎 | 45 |
| 7 | 林动 | 30 |
+-----+----------+------+
7 rows in set
```
3. 对所有人总分进行降序排序
```
mariadb> SELECT ids,name,(chinese+math+english) as 总分 FROM student ORDER BY (chinese+math+english) DESC;
+-----+----------+------+
| ids | name | 总分 |
+-----+----------+------+
| 4 | 南宫皓月 | 276 |
| 1 | 李明 | 257 |
| 3 | 南宫流云 | 242 |
| 5 | 南宫紫月 | 233 |
| 2 | 乘风 | 212 |
| 6 | 萧炎 | 185 |
| 7 | 林动 | 170 |
+-----+----------+------+
7 rows in set
```
4. 先选出数学成绩大于70的然后按照数学，以及中文成绩降序排列
```
mariadb> SELECT ids,name,math,chinese FROM student WHERE math > 70 ORDER BY math DESC,chinese DESC;
+-----+----------+------+---------+
| ids | name | math | chinese |
+-----+----------+------+---------+
| 1 | 李明 | 90 | 89 |
| 4 | 南宫皓月 | 90 | 88 |
| 3 | 南宫流云 | 77 | 87 |
+-----+----------+------+---------+
3 rows in set
```
### 常用函数
函数 | 说明
--- | ---
avg() | 返回列的平均值
count() | 返回列的行数
max() | 返回列中的最大值
min() | 返回列中的最小值
sum() | 返回列的总和
1. count()
count(*)统计包含null值的行，count(列名)统计中已剔除null值的行
统计当前student表中一共有多少学生
```
mariadb> SELECT COUNT(*) AS 人数 FROM student;
+------+
| 人数 |
+------+
| 7 |
+------+
1 row in set
```
2. avg()
统计所有人的数学平均分
```
mariadb> SELECT AVG(math) AS 数学平均成绩 FROM student;
+--------------+
| 数学平均成绩 |
+--------------+
| 65 |
+--------------+
1 row in set
```
