mysql基础用法

不知道写啥， 简单记录一下数据库的相关操作吧，结合实例

数据库

1. 用户、权限、密码问题

   -- 新建用户
   CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';
   
   -- 修改用户名
   UPDATE mysql.`user` set `user` = 'new_username' WHERE `user` = 'username';
   RENAME USER 'username'@'hostname' TO 'new_username'@'new_hostname';
   FLUSH PRIVILEGES;
   
   -- 用户密码修改
   SET PASSWORD FOR (username)=password('new_password');
   UPDATE mysql.`user` set `authentication_string`=password('new_password') where `user`='userna me'; # 5.7版本后用 authentication_string ，之前是 password字段
   # 修改完后 
   FLUSH PRIVILEGES;
   
   -- 删除用户
   DELETE FROM myql.`user` WHERE `user` = 'username' AND `host` = 'hostname';
   DROP USER 'username'@'hostname';
   
   -- GRANT 命令格式
   GRANT privileges ON databases.tablename TO 'username'@'hostname' [INDENTIFIED BY 'password']; 
   FLUSH PRIVILEGES;
   # 当使用 INDENTIFIED BY 时， 没有改用户，则会创建一个用户
   
   -- 查看用户权限
   SHOW GRANTS FOR 'username'@'hostname';
   
   -- GRANT 命令创建用户
   GRANT (privileges) ON *.* TO 'username'@'hostname' IDENTIFIED BY 'password';
   # eg. grant select on *.* to 'test'@'%' identified by '123456'
   # *.* -> database_name.table_name
   
   -- 设置用户访问权限
   GRANT (ALL PRIVILEGES) ON databases.tablename to 'username'@'hostname' [IDENTIFIED BY 'password'] [WITH GRANT OPTION] ;
   # ALL PRIVILEGES 可以替换为 select , update , insert , delete 其中之一或组合, 还有其他的，自己搜吧
   # WITH GRANT OPTION 加上表示给改用户添加赋权的权限
   # eg. grant select,update on *.* to 'test'@'%' with grant option
   
   -- 回收权限
   REVOKE (ALL PRIVILEGES) ON databases.tablename FROM 'username'@'hostname'
   # ALL PRIVILEGES -> select , update , insert , delete and ...
   # eg. revoke update, grant option on *.* from 'test'@'%'

2. 数据库新建&删除

   -- 查看
   SHOW DATABASES;
   -- 创建
   CREATE  DATABASE `test` DEFAULT CHARACTER SET utf8mb4;
   -- 删除
   DROP DATABASE `test`

数据表

说明： 在上面创建的test数据库中创建三张表， teacher(教师表)， students(学生表)， class(班级表)

Teachers (t_id, name, age, sex, email, phone)

Students(stu_id, name, age, sex, class_id)

Class(id, name, t_id) t_id -> 班主任 name其实可以细化为 年级号、 班号啥的

1. 创建

   -- teachers
   CREATE TABLE IF NOT EXISTS `teachers`(
   	`t_id` INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
   	`name` VARCHAR(30) NOT NULL DEFAULT '',
   	`sex` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0 -> 女; 1 -> 男',
   	`age` TINYINT NOT NULL DEFAULT 0,
   	`phone` VARCHAR(20) NOT NULL DEFAULT '',
   	`email` VARCHAR(50) NOT NULL DEFAULT '',
   	`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   	`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   ); 
   
   -- class
   CREATE TABLE IF NOT EXISTS `class`(
   	`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   	`name` VARCHAR(30) NOT NULL DEFAULT '',
   	`t_id` INT NOT NULL DEFAULT 0,
   	`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   	`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   );
   
   -- students;
   CREATE TABLE IF NOT EXISTS `students`(
   	`stu_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   	`name` VARCHAR(30) NOT NULL DEFAULT '',
   	`sex` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0 -> 女; 1 -> 男',
   	`age` TINYINT NOT NULL DEFAULT 0,
   	`class_id` INT NOT NULL DEFAULT 0,
   	`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   	`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   );
   
   -- 查看创建的表
   mysql> SHOW TABLES;
   +----------------+
   | Tables_in_test |
   +----------------+
   | class          |
   | students       |
   | teachers       |
   +----------------+
   
   mysql> DESC teachers;
   +------------+------------------+------+-----+-------------------+-----------------------------+
   | Field      | Type             | Null | Key | Default           | Extra                       |
   +------------+------------------+------+-----+-------------------+-----------------------------+
   | t_id       | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
   | name       | varchar(30)      | NO   |     |                   |                             |
   | sex        | tinyint(1)       | NO   |     | 0                 |                             |
   | age        | tinyint(4)       | NO   |     | 0                 |                             |
   | phone      | varchar(20)      | NO   |     |                   |                             |
   | email      | varchar(50)      | NO   |     |                   |                             |
   | created_at | timestamp        | NO   |     | CURRENT_TIMESTAMP |                             |
   | updated_at | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
   +------------+------------------+------+-----+-------------------+-----------------------------+

2. 修改

   -- 改表名
   ALTER TABLE `table` RENAME TO `new_table_name`;
   
   -- 添加字段
   ALTER TABLE `table` ADD [COLUMN] `column` ···
   -- ALTER TABLE `table_name` ADD COLUMN `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
   
   -- 修改字段
   ALTER TABLE `table` MODIFY [COLUMN] `column` ···
   
   -- 字段改名
   ALTER TABLE `table` CHANGE `column` `new_column` ···
   
   -- 删除字段
   ALTER TABLE `table` DROP COLUMN `column`

3. 删除

   DROP TABLE `table_name`

数据表的操作

• 增

  -- 向teacher表插入数据
  INSERT INTO `teachers`(`name`, `sex`, `age`) VALUES ('teacher1', 0, 35),('teacher2', 0, 30),('teacher3', 1, 35),('teacher4', 0, 37),('teacher5', 0, 35),('teacher6', 1, 29),
  ('teacher7', 0, 35),('teacher8', 0, 27),('teacher9', 1, 35),('teacher10', 0, 43);
  
  -- 向class表插入数据
  INSERT INTO `class`(`name`, `t_id`) VALUES('class1', 1),('class2', 7),('class3', 6),('class4', 9),('class5', 4);
  
  -- 向students表插入数据
  INSERT INTO `students`(`name`, `sex`, `age`, `class_id`) VALUES ('stu1', 0, 17, 1),('stu2', 1, 16, 3),('stu3', 1, 17, 4),('stu4', 0, 16, 3),
  ('stu5', 0, 17, 3),('stu6', 1, 17, 5),('stu6', 0, 16, 3),('stu8', 1, 18, 2),('stu9', 0, 17, 3),('stu10', 1, 16, 4),('stu11', 1, 17, 3),
  ('stu12', 0, 17, 2),('stu13', 0, 18, 1),('stu14', 1, 17, 5),('stu15', 0, 18, 4),('stu16', 0, 17, 3),('stu17', 1, 18, 2),('stu17', 0, 17, 2),
  ('stu19', 0, 17, 5),('stu20', 1, 18, 4),('stu21', 1, 16, 3),('stu22', 0, 17, 3),('stu23', 0, 17, 1),('stu24', 1, 16, 3),('stu25', 1, 17, 1),
  ('stu26', 0, 18, 3),('stu27', 1, 17, 4),('stu28', 0, 16, 3),('stu29', 0, 17, 4),('stu39', 1, 18, 3);
  
  -- 创建好的表， 自己做一个备份吧，后面可能要还原数据

• 删

  -- 简单删除
  DELETE FROM teachers WHERE t_id = 10;

• 改

  -- 简单更改
  UPDATE `teachers` SET email = 'test@gmail.com' WHERE `name` = 'teacher3'

• 查

  -- 简单查询
  SELECT * FROM `teachers`; 
  
  SELECT `name`, class_id FROM `students`;

按需完成相关操作

1. 查询每个班的班主任信息

   SELECT * FROM `teachers` WHERE t_id IN (
      SELECT t_id FROM `class` 
   );
   
   SELECT teachers.* FROM `class` JOIN `teachers` on class.t_id = `teachers`.t_id ;

2. 查询每个班的人数

   SELECT a.id, a.`name`, count(b.class_id) FROM `class` AS a  LEFT JOIN `students` AS b ON a.id = b.class_id GROUP BY a.id ;

3. 查询每个班主任所带班级的平均年龄, 包括班主任信息

   SELECT a.t_id, a.`name`, a.`age`, a.`sex`, a.`email`, a.`phone`, b.`name` as class_name, avg(c.`age`) as class_avg_age FROM `teachers` as a
   JOIN `class` as b on a.t_id = b.t_id
   JOIN `students` as c on b.id = c.class_id
   GROUP BY a.t_id,b.id ;

4. 将teacher4老师所带班级的名叫stu6的学生的年纪修改为18岁

   -- 多表连接更新
   UPDATE teachers as a 
   JOIN class as b on a.t_id = b.t_id
   JOIN students as c on b.id = c.class_id
   SET c.age = 18
   WHERE a.`name` = 'teacher4' and c.`name` = 'stu6'

5. 将teacher6老师所带班级的名叫stu39的学生的记录删除

   -- 多表连接删除
   DELETE c from teachers as a 
   JOIN class as b on a.t_id = b.t_id
   JOIN students as c on b.id = c.class_id
   WHERE a.`name` = 'teacher6' and c.`name` = 'stu39'

mmp ， 先写这么多吧，后面用到了在加上。