mysql学习笔记

DDL：Data Definition Languages，数据定义语言。对数据库内部的对象进行创建、删除、修改的操作语言。这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter等。
DML：Data Manipulation Language，数据操纵语句。只是对表内部数据的操作，而不涉及到表的定义、结构的修改，更不会涉及到其他对象。用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、udpate 和select 等。
DCL：Data Control Language，数据控制语句。用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。
查询所有数据库：SHOW DATABASES;

创建数据库：

CREATE DATABASE itcast;
CREATE DATABASE if NOT EXISTS itcast;
CREATE DATABASE itheima DEFAULT CHARSET UTF8MB4;

删除数据库：DROP DATABASE if EXISTS itheima;
使用数据库：USE itcast;
查询当前数据库：SELECT DATABASE();
查询当前数据库所有表：SHOW TABLES;
查询表结构：DESC tb_user;
查询指定表的建表语句：SHOW CREATE TABLE tb_user;
数据类型-数值类型：TINYINT SMALLINT MEDIUMINT int integer BIGINT FLOAT DOUBLE DECIMAL
无符号类型：UNSIGNED
有符合类型：SIGNED
字符串类型：CHAR VARCHAR TINYBLOB TINYTEXT BLOB TEXT MEDIUMBLOB MEDIUMTEXT LONGBLOB LONGTEXT。 char 定长字符串，性能好；varchar 变长字符串，性能较差。
日期时间类型：DATE TIME YEAR DATETIME TIMESTAMP

创建表：

CREATE TABLE tb_user(
 id INT COMMENT '编号',
 `name` VARCHAR(50) COMMENT '年龄',
 age INT COMMENT '年龄',
 gender VARCHAR(1) COMMENT '性别'
)COMMENT '用户表';

CREATE TABLE emp(
  id INT COMMENT '编号',
  workno VARCHAR(10) COMMENT '性别',
  `name` VARCHAR(10) COMMENT '姓名',
  gender CHAR(1) COMMENT '年龄',
  age TINYINT UNSIGNED COMMENT '年龄',
  idcard CHAR(18) COMMENT '身份证号',
  entrydate DATE COMMENT '入职时间'
)COMMENT '员工表';

修改表-添加字段：ALTER TABLE emp ADD nickname VARCHAR(20) COMMENT '昵称';
修改数据类型：ALTER TABLE emp MODIFY nickname VARCHAR(25);
修改字段名和字段类型：ALTER TABLE emp CHANGE nickname username VARCHAR(30) COMMENT '用户名';
删除字段：ALTER TABLE emp DROP username;
修改表名：ALTER TABLE employee RENAME TO emp;
删除表：DROP TABLE if EXISTS emp2;
删除表，并重新创建该表：TRUNCATE TABLE emp2;

给指定字段添加数据：

INSERT INTO employee(id,workno,`name`,gender,age,idcard,entrydate) 
VALUES (1,'1','Itcast','男',10,'123456789','2000-01-01');

给全部字段添加数据：

INSERT INTO employee VALUES (2,'2','张无忌','男',18,'45645645','2005-01-01');

批量插入数据：

INSERT INTO emp(id,workno,`name`,gender,age,idcard,entrydate) VALUES 
(3,'3','韦一笑','男',18,'45645645','2005-01-01'),
(4,'4','韦一笑2','男',18,'45645645','2005-01-01');

INSERT INTO employee VALUES
(5,'5','韦一笑','男',18,'45645645','2005-01-01'),
(6,'6','韦一笑2','男',18,'45645645','2005-01-01');

修改数据：

UPDATE employee SET `name`='Itheima' WHERE id=1;
UPDATE employee SET `name`='小赵',gender='女' WHERE id=1;
UPDATE employee SET entrydate='2008-01-01';

删除数据：

DELETE FROM employee WHERE id=8;
DELETE FROM employee;

查询多个字段：

SELECT id,`name` FROM employee;
SELECT * FROM employee;

设置别名 as 可以省略：

SELECT id AS ID,`name` AS myName FROM employee;

去除重复记录：
SELECT DISTINCT `name` FROM employee;
条件查询-条件运算符：
- > 大于
- >=大于等于
- < 小于
- <= 小于等于
- = 等于
- <> 或 ！= 不等于
- between ... and ...在某个范围之内
- in(...) 在in之后的列表中的值
- like占位符模糊匹配 _匹配单个字符 % 匹配多个字符
- is NULL是 NULL
逻辑运算符：
- AND 或 && 并且
- OR 或者 || 或者
- NOT 或 ! 非，不是

条件查询举例：

SELECT * from emp WHERE age=18;
SELECT * FROM emp WHERE age<=20;
SELECT * FROM emp WHERE age!=20;
SELECT * FROM emp WHERE age<>20;
SELECT * FROM emp WHERE idcard IS NULL;
SELECT * FROM emp where idcard IS NOT NULL;
SELECT * FROM emp where age>=15 and age<=20;
SELECT * FROM emp WHERE age BETWEEN 15 AND 20;
SELECT * FROM emp WHERE gender='女' AND age<25;
SELECT * FROM emp WHERE age=18 OR age=20 OR age=40;
SELECT * FROM emp WHERE age IN (18,20,40);
SELECT * FROM emp WHERE `name` LIKE '__';
SELECT * FROM emp WHERE `name` LIKE '%敏';

聚合函数，null值不参加运算

SELECT COUNT(*) FROM emp;
SELECT COUNT(id) FROM emp;
SELECT AVG(age) FROM emp;
SELECT max(age) FROM emp;
SELECT min(age) FROM emp;
SELECT sum(age) FROM emp WHERE workaddress='上海';

分组查询 group by。where分组之前过滤，不参与分组。having分组之后对结果过滤。 where 不能对聚合函数进行判断，having可以。执行顺利：where > 聚合函数 > having。分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

SELECT gender,COUNT(*) FROM emp GROUP BY gender;
SELECT gender,avg(age) FROM emp GROUP BY gender;
SELECT workaddress,COUNT(*) FROM emp WHERE age<45 GROUP BY workaddress;
SELECT workaddress,COUNT(*) FROM emp WHERE age<45 GROUP BY workaddress HAVING COUNT(*)>=3;
SELECT workaddress,COUNT(*) address_count FROM emp WHERE age<45 GROUP BY workaddress HAVING address_count>=3;

排序查询 order by asc 升序（默认) desc 降序

SELECT * FROM emp ORDER BY age;
SELECT * FROM emp ORDER BY age ASC;
SELECT * FROM emp ORDER BY age DESC;
SELECT * FROM emp ORDER BY age ASC,entrydate DESC;

分页查询 limit 起始索引,查询记录数。起始索引从0开始 起始索引=（查询页面-1）*每页显示记录数。如果查询第一页数据，起始索引可用省略，简写 limit 10
SELECT * FROM emp LIMIT 0,10;
SELECT * FROM emp LIMIT 10;
SELECT * FROM emp LIMIT 10,10;

练习

SELECT * FROM emp WHERE gender='女' AND age IN (20,21,22);
SELECT * FROM emp WHERE gender='女' AND (age BETWEEN 20 AND 40) AND `name` LIKE '__';
SELECT gender,COUNT(*) FROM emp WHERE age<60 GROUP BY gender;
SELECT `name` FROM emp WHERE age<=35 ORDER BY age,entrydate DESC;
SELECT * FROM emp WHERE gender='男' AND age BETWEEN 20 AND 40 ORDER BY age ASC,entrydate asc LIMIT 5;

select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分分组后条件列表 order by 排序字段列表 limit 分页参数。

执行顺序 ※from ※ where ※ group by having※ select ※ order by ※ limit
SELECT `name`,age FROM emp WHERE age>15 ORDER BY age ASC;
用户管理主机名可用 % 通配
创建用户 itcast，只能在当前主机 localhost访问，密码 123456 ‘用户名‘@’主机名’
CREATE USER 'itcast'@'localhost' IDENTIFIED BY '123456';
创建用户 heima，可以在任意主机访问数据库，密码 123456

CREATE USER 'heima'@'%' IDENTIFIED BY '123456';

修改用户密码

ALTER USER 'itcast'@'localhost' IDENTIFIED WITH mysql_native_password BY '1234';

删除用户
DROP USER 'itcast'@'localhost';