MySql基础

1
net start mysql80 启动mysql服务
2
net stop mysql80 停止mysql服务
3
mysql -u root -p 连接mysql客户端
4
密码123456

1
添加字段
2
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
3
修改数据类型
4
alter table 表名 modify 字段名 新的数据类型(长度);
5
修改字段名和字段类型
6
alter table 表名 旧的字段名 新的字段名 数据类型(长度) [comment 注释] [约束];
7
删除字段
8
alter table 表名 drop 字段名;
9
修改表名
10
alter table 表名 rename to 新表名;
11
删除表
12
drop table  [if exists] 表名;
13
删除指定表并重新创建该表
14
truncate table 表名;

1
给指定字段添加数据
2
insert into table(字段名1,字段名2)values(值1,值2);
3
给全部字段添加数据
4
INSERT INTO 表名 VALUES (值1,值2,…);
5
批量添加数据
6
INSERT INTO 表名 (字段名1,字段名2,…) VALUES (值1,值2,…),(值1,值2,….,(值1,值2,…)
7
INSERT INTO 表名 VALUES(值1,值2,…), (值1,值2,….,(值1,值2,….);
8

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

1
UPDATE 表名 SET字段名1=值1，字段名2=值2，……[WHERE 条件];
2
//修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

1
DELETE FROM 表名 [WHERE条件]
2
//DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
3
//DELETE 语句不能删除某一个字段的值(可以使用UPDATE)。

1
查询多个字段
2
SELECT 字段1,字段2,字段3 FROM 表名;
3
SELECT * FROM 表名;
4
设置别名
5
SELECT 字段1[AS 别名1]，字段2[AS 别名2〕…FROM表名;
6
去除重复记录
7
SELECT DISTINCT 字段列表 FROM 表名;
8
//通配符的优势是，当不知道所需要的列的名称时，可以通过它获取它们。
9
在生产环境下，不推荐直接使用 SELECT ∗ 进行查询。

1
SELECT 字段列表 FROM 表名 WHER E条件列表
2

3

4
between and 在某个范围之内(含最大值和最小值)
5
in() 在in 之后的列表中的值，多选一
6
like 占位符 模糊匹配（——匹配单个字符，%匹配任意字符）
7
is null 是null
8
and 并且（多个条件同时成立）
9
or 或者（多个条件任意一个成立)
10
not 非不是

1
10.查询姓名为两个字的员工信息_%
2
select * from emp where name like '__';
3
11.查询身份证号最后一位是X的员工信息
4
select * from emp where idcard like '%X';
5

6
12.查询last_name中第2个字符是_且第3个字符是a的员工信息
7
-- 需要使用转义字符：\
8
SELECT last_name,salary,department_id
9
FROM employees
10
WHERE last_name LIKE '_\_a%';
11
13.选择姓名中有字母a和k的员工姓名
12
SELECT last_name
13
FROM employees
14
WHERE last_name LIKE ("%a%") AND last_name LIKE ("%k%");

1
count 统计数量
2
max 最大值
3
min 最小值
4
avg 平均值
5
sum 求和
6

7
SELECT 聚合函数(字段列表) FROM表名

1
统计该企业员工的最大年龄
2
select max(age) from emp;
3
4.统计该企业员工的最小年龄
4
select min(age)from emp;
5
-5.统计西安地区员工的年龄之和
6
select sum(age) from emp where workaddress='西安';

1
SELECT 字段列表 FROM 表名 [WHERE条件] GROUP BY分组字段名[HAVING 分组后过滤祭件]
2
where与having区别
3
执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
4
判断条件不同：where不能对聚合函数进行判断，而having可以。
5
执行顺序:where >聚合函数>having
6
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

1
1.根据性别分组，统计男性员工和女性员工的数量
2
select gender, count(*) from emp group by gender ;
3
2．根据性别分组，统计男性员工 和女性员工的平均年龄select gender, avg(age) from emp group by gender ;
4
3．查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
5
select workaddress, count(*) address_count from emp where age < 45 group by workaddress having address_count >= 3;

1
SELECT 字段列表 FROM 表名 ORDER BY 字段1排序方式1，字段2排序方式2;
2
ASC：升序（默认值）DESC：降序
3
//如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。
4
//列的别名只能在ORDER BY 中使用，不能在WHERE中使用，因为WHERE的执行顺序先于别名，故在WHERE使用时会报错

1
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
2
起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数。
3
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
4
如果查询的是第一页数据，起始索引可以省略， 直接简写为imit 10。

1
询年龄为20，21，22，23岁的女性员工信息。
2
select * from emp where gender ='女' and age in(20,21,22,23);
3
2.查询性别为男，并且年龄在20-40岁（含）以内的姓名为三个字的员工。
4
select * from emp where gender =男' and ( age between 20 and 40) and name like '---';
5
3，统计员工表中，年龄小于60岁的，男性员工和女性员工的人数。
6
select gender, count(*) from emp where age < 6θ group by gender;
7
4，查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。select name , age from emp where age <= 35 order by age asc , entrydate desc;
8
5.查询性别为男，且年龄在20-40岁（含）以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。
9
select * from emp where gender ='男' and age between 20 and 40 order by age asc , entrydate asc limit 5;

1
CREATE TABLE test1(
2
id INT NOT NULL,
3
last_name VARCHAR(25) NOT NULL,
4
email VARCHAR(50),
5
salary DECIMAL(10,2)
6
);

1
ALTER TABLE test1
2
MODIFY email VARCHAR(50) NULL;
3

4
INSERT INTO test1
5
VALUES(73,'赵六',NULL,4399);
6
-- 成功

1
CREATE TABLE test2(
2
id INT UNIQUE,    -- 列级约束
3
last_name VARCHAR(15),
4
email VARCHAR(25),
5
salary DECIMAL(10,2),
6
CONSTRAINT uk_test2_email UNIQUE(email) -- 表级约束
7
);

1
ALTER TABLE 表名称
2
MODIFY 字段名 字段类型 UNIQUE;

1
ALTER TABLE 表名称
2
ADD [CONSTRAINT 约束名] UNIQUE KEY(字段列表); --KEY 可以省略

1
CREATE TABLE student(
2
sid INT,   -- 学号
3
sname VARCHAR(20), -- 姓名
4
tel CHAR(11) UNIQUE KEY, -- 电话
5
cardid CHAR(18) UNIQUE KEY -- 身份证号
6
);

1
CREATE TABLE course(
2
cid INT, -- 课程编号
3
cname VARCHAR(20) -- 课程名称
4
);

1
CREATE TABLE student_course(
2
id INT,
3
sid INT,
4
cid INT,
5
score INT,
6
UNIQUE KEY(sid,cid) -- 复合唯一，每个学生的每门课只能有一次成绩
7
);

1
-- 查看都有哪些约束
2
SELECT *
3
FROM information_schema.table_constraints
4
WHERE table_name = '表名';
5

6
-- 根据查到的约束名来删除约束
7
ALTER TABLE 表名称
8
DROP INDEX 索引名;

1
ALTER TABLE test2
2
DROP INDEX uk_test2_email;

1
create table 表名称(
2
字段名 数据类型 primary key, -- 列级模式
3
字段名 数据类型,
4
字段名 数据类型
5
);
6

7
create table 表名称(
8
字段名 数据类型,
9
字段名 数据类型,
10
字段名 数据类型,
11
[constraint 约束名] primary key(字段名) -- 表级模式
12
);

1
ALTER TABLE 表名称
2
ADD PRIMARY KEY(字段列表);

1
create table 表名称(
2
字段名 数据类型,
3
字段名 数据类型,
4
字段名 数据类型,
5
primary key(字段名1,字段名2) -- 表示字段1和字段2的组合是唯一的，也可以有更多个字段
6
);

1
CREATE TABLE 表名称(
2
字段名 数据类型 PRIMARY KEY AUTO_INCREMENT,
3
字段名 数据类型 UNIQUE KEY NOT NULL,
4
字段名 数据类型 UNIQUE KEY,
5
字段名 数据类型 NOT NULL DEFAULT 默认值,
6
);
7

8
CREATE TABLE 表名称(
9
字段名 数据类型 DEFAULT 默认值 ,
10
字段名 数据类型 UNIQUE KEY AUTO_INCREMENT,
11
字段名 数据类型 NOT NULL DEFAULT 默认值,,
12
PRIMARY KEY(字段名)
13
);

1
ALTER TABLE 表名称
2
MODIFY 字段名 数据类型 AUTO_INCREMENT;

1
ALTER TABLE 表名称
2
MODIFY 字段名 数据类型 AUTO_INCREMENT;-- 给这个字段增加自增约束
3

4
ALTER TABLE 表名称
5
MODIFY 字段名 数据类型; -- 去掉AUTO_INCREMENT相当于删除

1
ALTER TABLE 从表名
2
ADD [CONSTRAINT 约束名]
3
FOREIGN KEY (从表的字段)
4
REFERENCES 主表名(被引用字段)
5
[ON UPDATE xx][ON DELETE xx];
6
添加外键
7
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
8
aLter table emp drop foreign key fk_emp_dept_id;

1
CREATE TABLE dept(
2
did INT , #部门编号
3
dname VARCHAR(50) #部门名称
4
);
5

6
CREATE TABLE emp(
7
eid INT PRIMARY KEY, #员工编号
8
ename VARCHAR(5), #员工姓名
9
deptid INT, #员工所在的部门
10
FOREIGN KEY (deptid) REFERENCES dept(did)
11
);
12
//被引用的字段不是主键，也没有唯一约束，故添加失败

1
CREATE TABLE dept(
2
did INT PRIMARY KEY, #部门编号
3
dname VARCHAR(50) #部门名称
4
);
5

6
CREATE TABLE emp(
7
eid INT PRIMARY KEY, #员工编号
8
ename VARCHAR(5), #员工姓名
9
deptid CHAR, #员工所在的部门
10
FOREIGN KEY (deptid) REFERENCES dept(did)
11
);
12
//从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。
13

14
//deptid为CHAR类型，did为INT类型，数据类型不一致

1
-- 1. 查看约束名和删除外键约束
2
SELECT *
3
FROM information_schema.table_constraints -- 查看某个表的约束名
4
WHERE table_name = '表名称';
5

6
ALTER TABLE 从表名
7
DROP FOREIGN KEY 外键约束名;
8

9
-- 2. 查看索引名和删除索引
10
SHOW INDEX FROM 表名称; -- 查看某个表的索引名
11
ALTER TABLE 从表名 DROP INDEX 索引名;

1
-- 如果这个字段原来有非空约束，你还想保留非空约束，那么在加默认值约束时，也得保留非空约束，否则非空约束就被删除了
2
ALTER TABLE 表名称
3
MODIFY 字段名 数据类型 DEFAULT 默认值;  -- 没加NOT NULL，非空约束就没了
4

5
-- 同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默认值约束，否则就删除了
6
ALTER TABLE 表名称
7
MODIFY 字段名 数据类型 DEFAULT 默认值 NOT NULL; -- 如果想保留非空约束，后面记得加上

1
-- 删除默认值约束，也不保留非空约束
2
ALTER TABLE 表名称
3
MODIFY 字段名 数据类型 ;
4

5
-- 删除默认值约束，保留非空约束
6
ALTER TABLE 表名称
7
MODIFY 字段名 数据类型 NOT NULL;

1
内连接演示
2
1，查询每一个员工的姓名，及关联的部门的名称（隐式内连接实现）表结构：emp，dept
3
连接条件：emp.dept_id=dept.id
4
select emp.name , dept.name from emp , dept where emp.dept_id = dept.id
5
select e.name,d.name from emp e , dept d where e.dept_id = d.id;
6
2，查询每一个员工的姓名，及关联的部门的名称（显式内连接实现）表结构：emp，dept
7
INNER JOIN...ON
8
连接条件：emp.dept_id=dept.id
9
select e.name, d.name from emp e inner join dept d on e.dept_id =d.id;
10
select e.name, d.name from emp e join dept d on e.dept_id=d.id;

1
1.查询emp表的所有数据，和对应的部门信息（左外连接)表结构：emp，dept
2
连接条件：emp.dept_id=dept.id
3
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
4
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;
5
2.查询dept表的所有数据，和对应的员工信息(右外连接)
6
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;
7
select d.*, e.* from dept d Left outer join emp e on e.dept_id = d.id;

1
SELECT @@autocommit;
2
SET @@autocommit = 0;
3

4
开启事务
5
START TRANSACTION 或 BEGIN;