MySQL学习笔记（part 1：基础介绍和语句）

一、数据模型一数据库类型1.关系型数据库RDBMS2.非关系型数据库MySQL属于关系型数据库二数据模型客户端--数据库管理系统DBMS--数据库--表三MySQL启动与停止1从MySQL连接net start mysql80 net stop mysql802客户端链接-- -h指定连接地址 -- -P指定连接端口 -- -u指定连接用户名 -- -p指密码 mysql [-h 127.0.0.1][-P 3306] -u root -p四MySQL的数据类型1.数值类型2.字符串类型3.日期时间类型MySQL常见数据类型二、基础SQL语言一通用语法1.SQL语句可以单行或多行书写以分号结尾2.SQL可以缩进对缩进位数无要求3.MySQL的SQL语句不区分大小写建议关键字大写4.注释-- 单行注释SQL通用 # 单行注释MySQL特有 /*多行注释 SQL通用*/二SQL语句分类分类全程说明DDLData Definition Language数据定义语言用来定义数据库对象数据库、数据库表、字段、索引DMLData Manioulation Language数据操作语言用来对数据库表中的数据进行增删改DQLData Query Language数据查询语言用来查询数据库中表的记录DCLData Control Language数据控制语言用来创建数据库用户控制数据库的访问权限三DDL语句1.数据库操作1查询-- 查询所有数据库 SHOW DATABASES; -- 查询当前数据库 SELECT DATABASE();2创建-- IF NOT EXISTS如果数据库名称不存在则创建。如果存在则不执行任何操作 -- DEFAULT CHARSET指定当前数据库使用的字符集如utf8mb4。可省略因为数据库有默认字符集。 -- COLLATE指定排序规则。 CREATE DATABASES [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];关于COLLATE3删除DROP DATABASE [IF EXISTS] 数据库名4使用-- 切换到该数据库 USE 数据库名2.表操作1查询-- 查询当前数据库所有表 SHOW TABLES; -- 查询表结构 DESC 表名 -- 查询指定表的建表语句 SHOW CREATE TABLE 表名2创建CREATE TABLE 表名( 字段1 字段1类型 [COMMENT 字段1注释] 字段2 字段2类型 [COMMENT 字段2注释] 字段3 字段3类型 [COMMENT 字段3注释] )[COMMENT 表注释]关于数据类型举例限定不带符号age TINYINTUNSIGNED限定三位整数、一位小数score DOUBLE(4,1)不限定长度文本usernameVARCHAR(50)限定文本长度genderCHAR(1)练习设计一张员工表/*设计一张员工表 员工工号固定长度 性别为男或女 年龄非负数 身份证号存在字母*/ create table employee( id int comment 员工id, worknumber varchar(10) comment 员工工号, name varchar(10) comment 姓名, gender char(1) comment 性别, age tinyint unsigned comment 年龄, idcard char(18) comment 身份证号, entrydate date comment 入职时间 ) comment 员工表;3修改①添加字段-- 添加字段 ALTER TABLE 表名 ADD 字段名 类型长度 [COMMENT 注释] [约束];练习为employee表增加一个字段-- 为员工表添加一个昵称字段 alter table emploee add nickname varchar(20) comment 昵称;②修改字段名和字段类型修改数据类型-- 修改指定字段数据类型 ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度)修改字段名和字段类型ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型长度 [COMMENT 注释] [约束]练习将nickname字段名修改为username类型为varchar(30)alter table employee change nickname username varchar(30) comment 用户名;③删除字段ALTER TABLE 表名 DROP 字段名;练习删除usernamealter table employee drop username;④修改表名ALTER TABLE 表名 RENAME TO 新表名;练习将表employee修改为employalter table employee rename to employ;⑤删除表-- 删除表 DROP TABLE [IF EXITS] 表名; -- 删除指定表并重新创建该表 -- 重新创建会清除数据留下表结构 TRUNCATE TABLE 表名;四DML语句1.增加数据INSERT)-- 给指定字段增加数据 INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,值2,...); -- 给全部字段添加数据 INSERT INTO 表名 VALUES(值1,值2,...); -- 批量添加数据 INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...); INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);注意①插入字段顺序与值的顺序一一对应②插入的是字符串或日期型的数据应包含在引号中③插入数据大小应在规定范围内。练习向employ中插入数据insert into employ(id, worknumber, name, gender, age, idcard, entrydate) values(1,1,itcast,男,20,123456789012345678,2000-01-01);2.修改数据UPDATE-- 修改数据 UPDATE 表名 SET 字段名1值1, 字段名2值2,...[WHERE 条件]注意修改语句的条件可以有也可以没有但没有条件的话会修改整张表的所有数据。练习修改employ中的数据-- 修改id为1的数据将name改为itsql update employ set nameitsql where id1; -- 修改id为1的数据将name改为 小绍gender为女 update employ set name小绍, gender 女 where id 1; -- 修改所有人的入职日期为2010-01-01 update employ set entrydate 2010-01-01;3.删除数据DELETEDELETE FROM 表名 [WHERE 条件];注意DELETE语句的条件可以有也可以没有但没有条件会删除整张表的所有数据。DELETE语句无法删除某一个字段的值。如果要修改用UPDATE设置为null。练习删除employ中的数据-- 删除gender为女的员工 delete from employ where gender女; -- 删除所有的员工 delete from employ;五DQL语句编写顺序与执行顺序SELECT -- 4 字段列表 FROM -- 1 表名列表 WHERE -- 2 条件列表 GROUP BY -- 3 分组字段列表 HAVING -- 3 分组后条件列表 ORDER BY -- 5 排序字段列表 LIMIT -- 5 分页参数1.基本查询select from1查询返回多个字段SELECT 字段1, 字段2,... FROM 表名; SELECT * FROM 表名;2设置别名SELECT 字段1[AS 别名1], 字段2[AS 别名2], ... FROM 表名;AS可以省略3去除重复记录SELECT DISTINCT 字段列表 FROM 表名;2.条件查询whereSELECT 字段列表 FROM 表名 WHERE 条件列表;条件运算符种类运算符功能比较运算符大于大于等于小于小于等于等于或!不等于BETWEEN...AND...在某个范围内包括上下限。between后为最小值and后为最大值。不能调转。IN(...)IN后列表中的值多选一LIKE 占位符模糊匹配_匹配单个字符%匹配任意个字符IS NULL判断字段是不是null逻辑运算符AND 或 且多个条件同时成立OR 或 ||或多个条件成立任意一个NOT 或 !非练习-- 查询年龄为40的员工 select * from employ where age 40; -- 查询年龄小于30的员工 select * from employ where age 30; -- 查询没有身份证号的员工 select * from employ where idcard is null; -- 查询有身份证号的员工 select * from employ where idcard is not null; -- 查询年龄不为40的员工 select * from employ where age ! 40; select * from employ where age 40; -- 查询年龄在20-30的员工包含20和30 select * from employ where age 20 and age 30; select * from employ where age between 20 and 30; -- 查询性别为女且年龄小于25岁的员工 select * from employ where gender 女 and age25; -- 查询年龄为20 30 或40的员工 select * from employ where age20 or age30 or age40; select * from employ where age in(20,30,40); -- 查询名字为两个字的员工 select * from employ where name like __; -- 查询身份证号最后一位为X的员工 select * from employ where idcard like %X;3.聚合参数count、max、min、avg、sum将一列数据作为一个整体进行纵向运算null值不参与计算函数功能count统计数量max最大值min最小值avg平均值sum求和SELECT 聚合函数(字段列表) FROM 表名;练习-- 统计企业员工数量 select count(*) from employ; select count(id) from employ; -- 统计企业员工的平均年龄 select avg(age) from employ; -- 统计最大年龄 select max(age) from employ; -- 统计西安地区年龄之和 select sum(age) from employ where workaddress 西安;4.分组查询group bySELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];where和having的区别执行时机不同where是分组之前过滤不满足where条件不参与分组having是分组之后的结果进行过滤判断条件不同where不能对聚合函数进行判断having可以对聚合函数进行判断练习-- 根据员工性别进行分组统计男性和女性员工的数量 select gender, count(*) from employ group by gender; -- 根据员工性别进行分组统计男性和女性员工的平均年龄 select gender, avg(age) from employ group by gender; -- 查询年龄小于45的员工并根据工作地址进行分组获取员工数量大于等于3的地址 select workaddress, count(*) from employ where age45 group by workaddress having count(*)3; -- 使用别名 select workaddress, count(*) as address_count from employ where age45 group by workaddress having address_count3;注意执行顺序where聚合函数having分组之后查询字段一般和聚合函数和分组字段查询其他字段没有意义只会返回第一个符合条件的数据。5.排序查询order bySELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;排序方式ASC升序默认DESC降序注意多字段排序时当第一个字段相同时才会按第二个字段排序。练习-- 根据年龄对公司员工进行升序排序 select * from employee order by age asc; -- 根据入职时间对公司员工进行降序排序 select * from employee order by entrydate desc; -- 根据年龄对公司员工进行升序排序年龄相同按入职时间进行降序排序 select * from employ order by age asc, entrydate desc;6.分页查询limitSELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;注意起始索引从0开始起始索引查询页码-1*每页记录数。分页查询不同数据库有不同的实现。MySQL用的是LIMIT。如果查询的第一页数据起始索引可以省略直接简写为limit示例-- 查询第一页员工数据每页显示10条记录 select * from employ limit 0,10; select * from employ limit 10; -- 查询第二页员工数据每页显示10条记录第11-20条记录 select * from employ limit 10,10;练习-- 查询年龄为20212223岁女性员工的信息 select * from employ where gender 女 and age in(20,21,22,23); -- 查询性别为男年龄在20-40岁以内含姓名为3个字的员工 -- 中间的括号可以分隔条件看起来更清晰 select * from employ where gender 男 and (age between 20 and 40) and name like ___; -- 统计员工表中年龄小于60岁的男性员工和女性员工的数量 select gender, count(*) from employ where age 60 group by gender; /*查询所有年龄小于35岁的员工的姓名和年龄 并对查询结果根据年龄升序排序如果年龄相同按入职时间降序排序*/ select name, age where age 35 order by age asc, entrydate desc; /*查询性别为男且年龄在20-40岁含以内的前五个员工信息 并对结果按年龄进行升序排序年龄相同按入职时间降序排序*/ select * from employ where gender 男 and age between 20 and 40 order by age asc, entrydate desc limit 5;六DCL语句1.管理用户1查询用户USE mysql; SELECT * FROM user;2创建用户CREATE USER 用户名主机名 INDENTIFIED BY 密码;本地主机localhost任意主机通配符%3修改用户密码ALTER USER 用户名主机名 IDENTIFIED WITH mysql_native_password BY 新密码;4删除用户DROP USER 用户名主机名;练习-- 创建用户it1只能在本机localhost上访问密码123456 create user it1localhost indentified by 123456; -- 创建用户it2能在任意主机上访问密码123456 create user it2% identified by 123456; -- 修改用户it2的密码为1234 alter user it2% identified with mysql_native_password by 1234; -- 删除用户it1 drop user it1localhost;2.权限控制MySQL常用权限权限说明ALL, ALL PRIVILEGES所有权限SELECT查询权限INSERT插入数据UPDATE修改数据ALTER修改表DROP删除数据库/表/视图CREATE修改数据库/表1查询权限SHOW GRANTS FOR 用户名主机名;2授予权限GRANT 权限列表 ON 数据库名.表名 TO 用户名主机名;如果授予所有数据库所有表的权限可写*.*3撤销权限REVOKE 权限列表 ON 数据库名.表名 FROM 用户名主机名;练习-- 查询it2的权限 show grants for it2%; -- 授予权限 grant all on data.* to it2%; -- 撤销权限 revoke all on data.* from it2%;注意多个权限用逗号分隔授权时数据库名和表名都可以用*通配代表所有