mysql笔记个人笔记（更新ing）

1、检查字符集
SHOW VARIABLES WHERE Variable_name LIKE ‘character_set_%’ OR Variable_name LIKE ‘collation%’;
2、MySQL字符集设置

• 系统变量：
– character_set_server：默认的内部操作字符集
– character_set_client：客户端来源数据使用的字符集
– character_set_connection：连接层字符集
– character_set_results：查询结果字符集
– character_set_database：当前选中数据库的默认字符集
– character_set_system：系统元数据(字段名等)字符集
– 还有以collation_开头的同上面对应的变量，用来描述字符序。

3、检测字符集问题的一些手段
• SHOW CHARACTER SET;
• SHOW COLLATION;
• SHOW VARIABLES LIKE ‘character%’;
• SHOW VARIABLES LIKE ‘collation%’;
• SQL函数HEX、LENGTH、CHAR_LENGTH
• SQL函数CHARSET、COLLATION

4、修改默认字符集
（1）最简单的修改方法，就是修改mysql的my.ini文件中的字符集键值
如 default-character-set = utf8
character_set_server=utf8
修改完后，重启mysql服务。
(2)mysql命令
mysql>set character_set_client=utf8
mysql>set character_set_connection=utf8
mysql>set character_set_database=utf8
mysql>set character_set_results=utf8
mysql>set character_set_server=utf8
mysql>set collation_connection=utf8
mysql>set collation_database=utf8
mysql>set collation_server=utf8

5、显示服务器中的数据库(mysqldata，mytable,分别是数据库数据表的名字）
mysql>show databases;
6、创建一个数据库
mysql>create database mysqldata;
7、选择数据库
mysql>use mysqldata;
8、查看表
mysql>show tables;
9、创建数据表
mysql>create table mytable (name VARCHAR(20),sex CHAR(1));
10、显示表结构
mysql>describe mytable;
11、往表中加入记录
mysql>insert into mytable values(“”,“”);
12、用文本方式将数据装入数据库表中
mysql>load data local infile “D:/mysql.txt” into table mytable;
13、导入.sql文件命令
mysql>use database;
mysql>source d:/mysql.sql
14、删除表
mysql>drop table mytable;
15、清空表
mysql>delete from mytable;
16、更新表中数据
mysql>update mytable set sex=“” where name=“”
17、为用户赋权限
一是在User表插入相应的数据行，同时设置相应的权限；二是通过GRANT命令创建具有某种权限的用户
grant all on mydb.* to NewUserName@HostName identified by “password”;
grant usage on . to NewUserName@HostName identified by “password”;
grant select ,insert,update on mydb.* to NewUserName@HostName identified by "password"l
grant update,delete on mydb.TestTable to NewUserName@HostName identified by “password”;

18、全局管理权限
FILE: 在MySQL服务器上读写文件。
PROCESS: 显示或杀死属于其它用户的服务线程。
RELOAD: 重载访问控制表，刷新日志等。
SHUTDOWN: 关闭MySQL服务。
数据库/数据表/数据列权限：
ALTER: 修改已存在的数据表(例如增加/删除列)和索引。
CREATE: 建立新的数据库或数据表。
DELETE: 删除表的记录。
DROP: 删除数据表或数据库。
INDEX: 建立或删除索引。
INSERT: 增加表的记录。
SELECT: 显示/搜索表的记录。
UPDATE: 修改表中已存在的记录。
特别的权限：
ALL: 允许做任何事(和root一样)。
USAGE: 只允许登录–其它什么也不允许做。

1. mysql创建与查询DDL：

查看当前数据库所有表：

show tables;

查询表结构：

desc 表名；

查询指定表的建表语句：

show create table 表名;

创建表：

create table 表名(
	字段1	类型		注释用comment表示,
	字段2	类型		注释,
	......      	#最后一行没逗号
)

表主键外键的添加：

create table biao1(
    a char(5),
    b char(5) primary key
     );

如果建表已经完成，再添加主键:

alter table 表名 add constraint  primary key (字段名);

例如alter table biao2 add constraint primary key (b);

查看当前所在数据库：

select database();

2. mysql的增添和修改DDL(alter改变.改动 )

给表添加字段

alter table 表名 add 添加字段名 字段类型（长度）  注释;

修改表中字段类型

alter table 表名 modify  字段名  新字段类型;

修改表中字段以及其类型：

alter table 表名 change 旧字段名  新字段名 新类型;

删除字段：

alter table 表名 drop 字段名;

删除表：

drop table[if exists] 表名;

删除表，并重新建立该表：

truncate table 表名;

修改表名：

alter table 表名 rename to 新表名;

3. 数据库表中数据增加（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,...);

注意:
·插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
·字符串和日期型数据应该包含在引号中。
·插入的数据大小，应该在字段的规定范围内。

4. 数据表中元素删除和更新DML

表中元素的修改：//可以改成null，以下两操作条件都可以省，表示对全部操作

UPDATE 表名 SET 字段名1=值1,字段名2=值2 ,....[ WHERE条件];

删除表中元素：//无法删除某一字段值

DELETE  FROM  表名 [WHERE条件];

5. 基础查询DQL

查询多个字段：

select 字段1,字段2... from 表名;

查询全部字段：

select* from 表名;

查询并且设置别名：

select 字段 [as] 字段新名 from 表名;

查询并且去重：

select distinct 字段 from 表名;

6. 条件查询DQL

select 字段 from 表名 where 条件;//条件可以多个

条件有以下类别

7. 聚合函数DQL

将1列元素作为整体聚合运算。

语法：

select 聚合函数(字段) from 表名;

注意:null值不参与所有聚合函数运算。

8. 分组查询DQL

select  字段列表 from 表名 [where 条件] group by 分组字段名 [having 条件];

where和having的区别：
执行时机不同: where是分组之前进行过滤，不满足where条件，不参与分组;而having是分组之后对结果进行过滤。
判断条件不同: where不能对聚合函数进行判断，而having可以。

注意：
执行顺序: where >聚合函数> having .
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

例如：

CREATE TABLE emp (
  id int DEFAULT NULL,
  workno varchar(10),
  name varchar(20),
  gender char(1),
  age int ,
  idcard varchar(18),
  workaddress varchar(50),
  entrydate date
)；

(1)根据性别分组，统计男性员工和女性员工的数量

select gender,count(*) from emp group by gender;

(2)根据性别分组，统计男性员工和女性员工的平均年龄

select gender,avg(age) from emp group by gender;

(3)查询年龄大于17的员工数，并根据工作地址分组，获取员工数量大于等于2的工作地址

select workaddress,count(*) as num from emp where age > 17 group by workaddress having num >= 2;

9. 排序查询

select  字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2；

ASC代表升序，DESC代表降序，多排序方式往往先按照第一个排序相同的按照第二个排序。
1.根据年龄对公司员工进行升序排列

select name from emp order by  age asc;

2.根据入职时间，对员工进行降序排列

 select * from emp order by  entrydate desc;

3.根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排序

 select * from emp order by age asc, entrydate desc;

10. 分页查询

select 字段列表 from 表名 limit 起始索引,查询记录数;

注意
·起始索引从0开始，起始索引=〈查询页码-1) * 每页显示记录数。
·分页查询是数据库的方言（差不多是区别的意思），不同的数据库有不同的实现，MySQL中是LIMIT。
·如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10（查询记录数为10）。

1.查询第一页员工数据，每页显示3条记录

select * from emp limit 0,3; ##此处0可以省略

2.查询第二页员工数据，每页显示3条记录

select * from emp limit 3,3;

此处加p21案例练习

DQL执行顺序：

DCL数据控制语言

1. 管理用户

查询用户：

use mysql;
select  * from user;

创建用户：

create user '用户名'@'主机名' identified by '密码';

修改用户密码:

alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

删除用户：

drop user  '用户名'@'主机名';

p24 2.03