mysql - 基础操作 (一)

1、数据库管理

• 1、 连接数据库
  mysql -u root -p

• 2 、查看所有的数据库
  show databases;

• 3、 创建数据库
  create database xiaocai default character set utf8; -- 指定默认字符集创建数据库

• 4、 查看数据库字符集
  show create database xiaocai;

• 5、 删除数据库
  drop database xiaocai;

• 6、 修改数据库字符集
  alter database xiaocai default character set gbk;

• 7、 使用某个数据库
  use xiaocai;

• 8、 查看该数据库中所有的表
  show tables;

• 9、 创建表
  create table student(id int,name varchar(20),age int);

• 10、 查看表结构
  desc student;

• 11、 删除表
  drop table student;

• 12、 添加字段
  alter table student add column gender varchar(2);

• 13、 删除字段
  alter table student drop column gender;

• 14、 修改字段类型
  alter table student modify column name varchar(100);

• 15、 修改字段名称
  alter table student change column name nickname varchar(10);

• 16、 修改表名称
  alter table student rename to user;

2、增删改

• 1、 插入数据
  insert into student values(1,'xiaocai',25); -- 不能少字段
  insert into student(id,name) values(2,'xiaobai'); -- 插入指定数据

• 2、 修改数据
  update student set age=21; -- 整体修改
  update student set age=25 where id = 1; -- 条件修改
  update student set age=22,name='xiaobaicai' where id = 2; -- 修改多个字段

• 3、 删除数据
  delete from student; -- 整体删除
  delete from student where id = 2; -- 条件删除

  truncate table student; -- 整体删除
  1）不能带条件删除 2）即可以删除表的数据，也可以删除表的约束 3）使用truncate table删除的数据不能回滚
  与 delete 不同：
  delete from: 可以全表删除 1)可以带条件删除 2）只能删除表的数据，不能删除表的约束 3)使用delete from删除的数据可以回滚（事务）

3、查

• 1、 查询整个表所有
  select * from student;

• 2、 查询指定列
  select id,name from student; -- 只需要id和name的字段

• 3、 查询的时候添加一个「常量列」

mysql> select id,name,'hello' from student;
+------+---------+-------+
| id   | name    | hello |
+------+---------+-------+
|    1 | xiaocai | hello |
|    1 | xiaobai | hello |
+------+---------+-------+

• 4、 查询时合并列 （合并列只能合并数值类型的字段）

mysql> select * from student;
+------+---------+------+------+---------+
| id   | name    | age  | math | english |
+------+---------+------+------+---------+
|    1 | xiaocai |   25 |   88 |      60 |
|    2 | baicai  |   21 |   10 |      20 |
+------+---------+------+------+---------+
2 rows in set (0.00 sec)

mysql> select id,name,(math+english) as 'sum' from student;
+------+---------+------+
| id   | name    | sum  |
+------+---------+------+
|    1 | xiaocai |  148 |
|    2 | baicai  |   30 |
+------+---------+------+

• 5、 查询时去除重复项

mysql> select * from student;
+------+----------+------+------+---------+
| id   | name     | age  | math | english |
+------+----------+------+------+---------+
|    1 | xiaocai  |   25 |   88 |      60 |
|    2 | baicai   |   21 |   10 |      20 |
|    3 | qingcai  |   21 |   66 |      77 |
|    3 | dabaicai |   21 |   44 |      87 |
+------+----------+------+------+---------+
4 rows in set (0.00 sec)

mysql> select distinct age from student;
+------+
| age  |
+------+
|   25 |
|   21 |
+------+

select distinct (age) from student; -- 也是一样的

3、复杂查询

• 1. 与或查询：and(与) or(或)
     SELECT * FROM student WHERE id = 2 AND NAME = 'xiaobai';
• 2. 数值大小查询

  >   <   >=  <=  =  <>(不等于)     between and (等价于>= 且 <=)
  

  SELECT * FROM student WHERE age >23;
  SELECT * FROM student WHERE age >22 AND age <26;
  SELECT * FROM student WHERE age BETWEEN 23 AND 26;
  SELECT * FROM student WHERE NAME <> 'xiaocai';

• 3. 判空条件
  • is null
  • is not null
  • =''
  • <>''
    SELECT * FROM student WHERE NAME IS NULL;
    SELECT * FROM student WHERE NAME IS NULL OR NAME = '';
• 4. 模糊查询 like
  • % : 表示任意个字符
  • _ : 表示一个字符

  SELECT * FROM student WHERE NAME LIKE '小%'; -- 查询所有name为‘小’开头的

  id    name    age
  5 小菜  25
  7 小梁  25
  

  SELECT * FROM student WHERE NAME LIKE '_菜'; -- 查询所有name为‘一个任意字后跟着菜字的’

  id  name    age  
  5   小菜  25   
  6   大菜  26   
  

• 5. 聚合查询（使用聚合函数的查询）
  • sum() 总和
  • avg() 平均
  • max() 最大
  • min() 最小
  • count() 函数统计的数量不包含null的数据

  SELECT SUM(age) AS '年龄之和' FROM student;
  SELECT MIN(age) AS '最小年龄' FROM student;
  SELECT AVG(age) AS '平均年龄' FROM student;
  SELECT COUNT(NAME) AS '所有学生name为不空的个数(可以为NULL)' FROM student ;

• 6. 分页查询 『limit 起始行,查询几行』
  • 起始行从0开始 『(当前页-1)*每页显示多少条』
  • 分页：每页显示多少条

  SELECT * FROM student LIMIT (当前页-1)*每页显示多少条,每页显示多少条;
  例如，每页查询个数为3，查询第一页
  SELECT * FROM student LIMIT 0,3;

  id  name     age  
  1   xiaocai  25   
  2   xiaobai  21   
  3            33   
  

  每页查询个数为3，查询第二页
  SELECT * FROM student LIMIT 3,3;

  id  name    age  
  4           41   
  5   小菜  25   
  6   大菜  26   
  

• 7. 查询排序（order by ）
  • asc: 顺序，正序。数值：递增，字母：自然顺序（a-z）
  • desc: 倒序，反序。数值：递减，字母：自然反序(z-a)

  SELECT * FROM student ORDER BY age DESC; -- 年龄倒叙

  查询『年龄倒叙，当年龄相同时id倒叙』排序
  SELECT * FROM student ORDER BY age DESC,id DESC;

  id  name     age  
  4            41   
  3            33   
  6   大菜   26   
  7   小梁   25   
  5   小菜   25   
  1   xiaocai  25   
  2   xiaobai  21   
  

• 8. 分组查询(group by)

  分组查询学生年龄，另加一行显示每个年龄的人数
  SELECT age AS '年龄',COUNT(*) FROM student GROUP BY age;

  年龄  count(*)  
  21      1         
  25      3         
  26      1         
  33      1         
  41      1         
  

• 9. 分组查询后筛选 (having)
     分组查询 学生年龄和相同年龄的个数 且年龄大于 22 的

  SELECT age,COUNT(age) AS '相同年龄个数' FROM student GROUP BY age HAVING age >22;

  age  相同年龄个数  
  25   3                   
  26   1                   
  33   1                   
  41   1                   
  

  • having一般跟在group by之后，执行记录组选择的一部分来工作的。
  • where则是执行所有数据来工作的。