mysql学习笔记

创建数据库

create database ‘数据库名称’; (注意末尾有英文分号)

create database one;

判读数据库不存在再创建数据库

create database if not exists ‘数据库名称’;

create database if not exists one;

删除数据库

drop database ‘数据库名称’;

drop database one;

判断数据库存在再删除数据库

drop database if exists ‘数据库名称’;

drop database if exists one;

查询所有数据库

show databases;   

(注意databases末尾有s)

使用数据库

use ‘数据库名称’

use one;

查询当前正在使用的数据库名称

select database();

数据库类型

1. int 整数类型
   age int

2. double 小数类型
   score double(5,2) 最大5位数,保留2位小数

3. date 日期类型 （只包含年月日,yyyy-MM-dd）

4. datetime 日期类型 （包含年月日时分秒,yyyy-MM-dd HH-mm-ss）

5. timestamp 时间戳类型 (包含年月日时分秒,yyyy-MM-dd HH-mm-ss)
   如果不给该字段赋值,或赋值为null，默认使用当前系统时间

6. varchar 字符串类型
   name varchar(20) 姓名最大20个字符
   zhangsan 8个字符 张三 2个字符

创建表

create table 表名(
列名1 数据类型1,
列名2 数据类型2,
列名3 数据类型3,
列名4 数据类型4 (注意最后一列没末尾有逗号)
);

create table stu(
    id int,
    name varchar(32),
	age int,
    score double(4,1),
    birthday date,
	insert_time timestamp
);

查询数据库所有表的名称

show tables;

查询表结构

desc 表名;

desc stu;

删除表

drop table ‘表名’

drop table stu;

判断表是否存在再删除

drop table if exists stu;

修改表名称

alter table 表名 rename to 新表名

alter table stu rename student;

添加一列

alter table ‘表名’ add 列名 数据类型;

alter table stu add math int;

修改列名称 类型

alter table 表名 change 列名 新列名 新数据类型;

alter table stu change math English int;

alter table 表名 modify 列名 新数据类型

alter table stu modify English double;

删除列

 alter table stu drop English;

DML 增删改表中的数据

添加数据：

语法：insert into 表名（列名1，列名2，列名3）values(值1,值2,值3);

INSERT stu (id,NAME,age) VALUES(2,'小明',19);

注意:
1.列名与值要一一对应
2.如果表名后不定义列名，则默认给使用列添加值

insert into stu values(值1,值2,值3,....值n);
INSERT INTO stu VALUES(1,'小明',19,80.5,'1999-02-03',NULL);

删除数据

delete from 表名 where 条件
delete from stu where id = 1;

如果没有写条件则删除删除所有记录

delete from stu;      	有多少条记录就执行多少次	
truncate from stu;  	先删除表,再创建一个一样的

修改数据

update 表名 set 列名1=值1,列名2=值2  where 条件; 
update stu  set age=19 , score = 90  where id = 1;

DQL查询语句

排序

order by 排序字段1 排序方式 , 排序字段2 排序方式2...
select from stu order by math asc , English desc ;

排序方式:
ASC : 升序 默认
DESC: 降序

聚合函数:将一列数据作为一个整体,进行纵向的计算。

1. count :计算个数.
   select count(列名) from 表名;

   select count(id) from stu;

2. max :计算最大值
   select max(列名) from 表名;

   select max(score) from stu;

3. min:计算最小值
   select min(列名) from 表名;

   select min(score) from stu;

4. sum:计算和
   select sum(列名) from 表名;

   select sum(score) from stu;

5. avg :计算平均值
   select avg(列名) from 表名;

   select avg(score) from stu;
   .注意:聚合函数的计算,排除nul1值。

分组查询

select group by 列名;

select sex ,avg(score) from stu group by sex;

分页查询

1.语法: limit 开始的索引,每页查询的条数

select * from stu limit 0,3;

公式 ： 开始索引 = （当前页码数 - 1）* 每条显示的条数

基础查询

新建表
CREATE TABLE student(
id INT, – 编号
NAME VARCHAR(20), – 姓名
age INT, – 年龄
sex VARCHAR(5), – 性别
address VARCHAR(100), – 地址
math INT, – 数学
english INT – 英语
);
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES (1,‘小米’,55,‘男’,’
杭州’,66,78),(2,‘小莉’,45,‘女’,‘深圳’,98,87),(3,‘小龙’,55,‘男’,‘香港’,56,77),(4,'小美
',20,‘女’,‘湖南’,76,65),(5,‘小溪’,20,‘男’,‘湖南’,86,NULL),(6,‘晓晓’,57,‘男’,
‘香港’,99,99),(7,‘小王’,22,‘女’,‘香港’,99,99),(8,‘小伟’,18,‘男’,‘南京’,56,65);

去除重复的结果集

select distinct address from student;

计算 math 和 english 分数之和

select name , math , english , math + english from student;

图中小溪的分数为NULL： 如果有NULL参与运算，计算结果都为NULL

让小溪有分数：ifnull

select name ,math , english , math + ifnull(english,0) from student;

math + ifnull(english,0) 太长且不好看： 起别名–as

select name, math , english , math + ifnull(english,0) as 总分 from student;

也可以去掉as

select name, math , english , math + ifnull(english,0) 总分 from student;

条件查询

查询年龄大于20

select * from student where age >=20;

查询年龄大于 20 小于 30 ：

用 and

select * from student where age >=20 and age <= 30;

用 between and

select * from student where age between 20 and 30 ;

查询年龄22 ， 18 ， 25 岁的信息:
用or

select * from student where age = 22 or age = 18 or age = 25 ;

用 in
select * from student where age in (22,18,25);

查询英语成绩为 null
select * from student where english is null;

select * from student where english is not null;

模糊查询

–查询姓小的有哪些 ： like

select * from student where name like '小%';

–查询第二个字是龙的人

select * from student where name like '_龙%';

–查询姓名有三个字的人：

select * from student where name like '___';

–查询姓名中包含龙的龙的人

select * from student where name like '%龙%';

约束

非空约束 not null

创建表时添加约束

create table stu {
	id int , 
	name varchar(20) not null;  -- 给name 添加非空约束
};

创建表完后，添加非空约束

	alter table stu modify name varchar(20) not null;

删除name的非空约束

	alter table stu modify name varchar(20);

唯一约束 unique 某一列的值不能重复

create table stu(
	id int, 
	phone_number varchar(20) unique  -- 添加了唯一约束
);

• 注意mysql中，唯一约束限定的列的值可以有多个null

删除唯一约束

alter table stu drop index phone_number;

在创建表后，添加唯一约束

alter table stu modify phone_number varchar(20) unique;

主键约束 primary key

注意:
1,含义:非空且唯一
2·一张表只能有一个字段为主键
3,主键就是表中记录的唯一标识

在创建表时，添加主键约束

create table stu(
	id int primary key, -- 给id添加主键约束
	name varchar(20)
);

删除主键

alter table stu drop primary key;

创建表后，添加主键

alter table stu modify id int primary key;

自动增长 auto_increament 值可以自动增长

在创建表时，添加主键约束，并且完成主键自增长

	create table stu(
		id int primary key auto_increment,-- 给id添加主键约束
		name varchar(20)
	);

删除自动增长

alter table stu modify id int ;

外键约束（减少冗余）

CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);
– 创建员工表(id,name,age,dep_id)
– 多方，从表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT, – 外键对应主表的主键
CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);
– 添加 2 个部门
INSERT INTO department VALUES(NULL, ‘研发部’,‘广州’),(NULL, ‘销售部’, ‘深圳’);
SELECT * FROM department;
– 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES (‘张三’, 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES (‘李四’, 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES (‘王五’, 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES (‘老王’, 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES (‘大王’, 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES (‘小王’, 18, 2);
SELECT * FROM employee;

添加外键语法

create table 表名(
…
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);

在创建表时，可以添加外键

create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外键对应主表的主键
constraint emp_dept_fk foreign key (dep_id) references  department(id)
);

删除外键

alter table 表名 drop foreign key 外键名称;

alter table employee drop foreign key emp_dept_fk;

创建表之后，添加外键

alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称);

alter table employee add constraint emp_dept_fk foreign key (dep_id) references department(id);

级联操作(方便修改,该处修改部门id就可以修改全部员工所属的部门id)

添加级联操作 语法：
alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称) on update cascade;

分类：
1.级联更新：on update cascade

alter table employee add constraint emp_dept_fk foreign key (dep_id) references department(id) on update cascade ;

2.级联删除： on delete cascade

alter table employee add constraint emp_dept_fk foreign key (dep_id) references department(id) on delete cascade ;

DCL：管理用户，授权

管理用户

添加用户：

语法：create user ‘用户名’@‘主机名’ identified by ‘密码’;

create user 'lishi'@'localhost' identified by '123';

删除用户：

语法：DROP USER ‘用户名’@‘主机名’;

drop user 'lisi'@'localhost';

修改用户密码：

SET PASSWORD FOR ‘用户名’@‘主机名’ = PASSWORD(‘新密码’);

set password for 'lisi'@'localhost'  =  password('1234');

mysql中忘记了root用户的密码？

1. cmd -- > net stop mysql 停止mysql服务
	* 需要管理员运行该cmd

2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables

3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功

4. use mysql;

5. update user set password = password('你的新密码') where user = 'root';

6. 关闭两个窗口

7. 打开任务管理器，手动结束mysqld.exe 的进程

8. 启动mysql服务

9. 使用新密码登录。

查询用户：

切换到mysql数据库

use user;

查询user表

select * from user;

通配符： % 表示可以在任意主机使用用户登录数据库

权限管理：

查询权限：
SHOW GRANTS FOR ‘用户名’@‘主机名’;

show grants for 'lisi'@'localhost';

授予权限：
grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
– 给张三用户授予所有权限，在任意数据库任意表上

grant all on *.* to 'lisi'@'localhost';

撤销权限：
revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;

revoke update on db3 account from 'lisi'@'localhost';