MySQL学习笔记

MySQL学习笔记

SQL: Structure Quevy Language (结构化查询语言)

一、基本知识

DDL:数据定义语言

DML:数据操作语言 --操作数据库表中的记录
DQL:数据查询语言
DCL:数据控制语言 --定义访问权限和安全级别

1、登录方式

mysql -uroot -p123456   								-- 123456是密码
alter user'root'@'localhost'indentified by'新密码'; 	  -- 更改密码
mysqladmin -u root -p password 123456  					-- 在命令行中更改密码

2、数据类型

数值类型：double/int/float

eg:double(5,2)最多有五位，必须有两位小数，最大值：999.99

3、字符串类型

varchar/char/tinytext

4、日期和时间类型

DATE,TIME,YEAR

***字符串类型和日期类型都要用单括号括起来，‘2020-01-01’
***一列是一个字段，一行是一条记录

二、数据表操作

1、建立数据表

create table teacher(				-- 创建teacher表
	id int(4) not null primary key auto_increment,
	name char(20) not null,
	sex char(10) not null,
	addr char(20) not null
);

create table student(
	id bigint,
	name varchar(20),
	email varchar(20),
	age int
);

2、显示表结构

show tables;  -- 需要当前use teacher
desc teacher  -- 或者使用desc查看表的字段信息

3、插入数据

-- insert into 表名(字段1，字段2........) value(列值1，列值2......); 

INSERT INTO teacher(name,sex,addr) VALUES('Leo','Male','hangzhou');		
-- 或者插入多条记录：
insert into 表名(字段1，字段2........) value(列值1，列值2......)，(列值1，列值2......)，(列值1，列值2......); 
INSERT INTO teacher(name,sex,addr) VALUES('Able','Male','beijing');
INSERT INTO teacher(name,sex,addr) VALUES('John','Male','shanghai');
INSERT INTO teacher(name,sex,addr) VALUES('Mary','Female','hangzhou');

三、查询语句

1、基础查询语句

SELECT * FROM teacher; 					 -- 返回整个表的虚拟的结果集（存放在内存中）
select stu_name,stu_age from students; 	 -- 查询指定的列

–查询学号为1001，1002，1003的记录

select * from teacher where id=1001 or id=1002 or id=1003;
select * from teacher where id in (1001,1002,1003);  

–查询name为null的记录

select * from teacher where name is null;

–查询性别不为男的记录

select * from teacher where gender!='男';

–查询年龄在18到20之间的记录

select * from teacher where age between 18 and 20; 
select * from teacher where age>=18 and age <=20;

2、模糊查询

–查询姓名由5个字母构成的学生记录

select stu_name from students where name like '_____';    
-- 五个下划线，_表示的是任意一个字符

–查询姓名由5个字母构成的学生记录，并且最后一个字母是s

select stu_name from students where name like 's';    -- 四个下划线

–查询姓名以‘m’开头的学生记录

SELECT * FROM teacher where name like 'm%';     	-- %表示任意多个字符

–字段控制查询

–去重操作

select distinct stu_name from students ;

–对查询字段运算

select * ,age+score from students;  
-- 生成新的一个字段，名称为age+score，字段的内容为age+score的值

SELECT * ,IFNULL(age,0)+IFNULL(scoore,0) FROM students;
SELECT * ,IFNULL(age,0)+IFNULL(scoore,0) AS total FROM students;  
-- 列名更改为total

---

四、排序

1、基础排序

SELECT * FROM employee ORDER BY salary; 		-- 按照salary字段的升序排序
SELECT * FROM employee ORDER BY salary DESC;	-- 按照salary字段的降序排序
SELECT * FROM employee ORDER BY salary DESC，id DESC;
-- 按照salary字段的降序排序,如果salary相等，按照id的降序排序。

2、聚合函数

SELECT COUNT(*) FROM employee;
SELECT COUNT(performance) FROM employee;

–统计月薪和绩效之和大于5000的人数个数

SELECT COUNT(*) FROM employee WHERE IFNULL(salary,0)+IFNULL(performance,0) >5000;

–统计月薪之和

SELECT SUM(salary+IFNULL(performance,0)) FROM employee;

–统计平均月薪

SELECT AVG(salary) FROM employee;

–统计最大值和最小值

SELECT MAX(salary),MIN(salary) FROM employee;

3、分组查询

having是在分组之后进行过滤，后面可使用聚合函数

where是在分组之前进行过滤

SELECT department,GROUP_CANCAT(`name`) FROM employee GROUP BY department;

-- 通过GROUP_CONCAT()来查看分开的记录，作为输出字段来显示

SELECT department ,GROUP_CONCAT(salary) from employee where salary between 1500 and 2000 GROUP BY department;

–查询工资总和大于9000的部门名称

SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee GROUP BY department HAVING SUM(salary) >=9000;

–查询工资大于2000，工资总和大于6000的部门名称和工资和,并按照降序排序

SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee where salary>2000 GROUP BY department HAVING SUM(salary)>6000 ORDER BY SUM(salary) DESC;

-- LIMIT 使用

SELECT * FROM employee LIMIT 3,3;
-- 从表中取出4，5，6行的数据  LIMIT的第一个参数是从哪一行开始查，第二个参数是一共要查几行

–分页过程

SELECT * FROM employee LIMIT (cur_page-1)*pageSize,pageSize;

五、数据完整性

实体完整性（表中一条记录就是一个实体）

域完整性

引用完整性

1、实体完整性

约束类型：主键约束，唯一约束，自动增长约束

1.1主键约束primary key

每个表中要有一个主键，数据唯一且不为null

CREATE TABLE 表名（字段1，数据类型，PRIMARY KEY ,字段2 数据类型，字段3...........）		     -- 定义主键

CREATE TABLE 表名（字段1，数据类型,字段2 数据类型，字段3,数据类型，PRIMARY KEY（字段1，字段2））
-- 联合主键

ALTER TABLE student ADD CONSTRAINT PRIMARY KEY(ID);
-- 添加主键

1.2唯一约束

数据不能重复，可以为空

CREATE TABLE 表名（字段1，数据类型，字段2，数据类型，UNIQUE）

– 自动增长列（通常把主键设置为自动增长列）
指定列的数据自动增长，即使删除数据，序号也是继续向下增长

CREATE TABLE student(
	id int PRIMARY KEY,auto_increment,
	name varchar(20) UNIQUE
);

2、域完整性

CREATE TABLE stu(
	id int PRIMARY KEY auto_increment,
	name varchar(20) UNIQUE NOT NULL,
	gender CHAR(1) DEFAULT '男'
);

六、其他操作

1、更新数据（修改表的列名）

UPDATE teacher set name = 'lunan' where id =1;	      

update student set stu_age=20,stu_score=70 where stu_name='ls';	
-- 把姓名为李四的年龄改为20，分数改为70

alter table student change stu_name s_name varchar(30);   
-- 更改列名 从stu_name改为s_name

2、删除操作

DELETE FROM teacher where name='John';  
-- 删除teacher表中字段名为John的一列

alter table teacher drop John;        
-- 删除teacher表中字段名为John的一列

drop table teacher；    		   -- 删除teacher表,表就不存在了
delete from teacher;		    -- 删除表的内容，表依然存在，id累加，从下一个开始
truncate table teacher;		    -- 直接先drop一个表，然后再创建一个同样的新表，内容是空的，id从1开始，速度比delete快

3、扩展

–完整创建表，并添加数据：

create table student(
	id int(4) not null primary key auto_increment,
	name char(20) not null,
	sex char(10) not null,
	addr char(20) not null
);
INSERT INTO student(name,sex,addr) VALUES('nanlu','Male','hangzhou');
INSERT INTO student(name,sex,addr) VALUES('Peter','Male','beijing');
INSERT INTO student(name,sex,addr) VALUES('Sarah','Male','shanghai');
INSERT INTO student(name,sex,addr) VALUES('Lossy','Female','hangzhou');

–给一个表添加新的一列

alter table student add stu_gender tinyint;
-- alter table 表名 add 列名 数据类型；

–修改表中某个字段的类型

-- alter table 表名 modify 字段名 数据类型；
alter table student modify stu_name varchar(30); -- 将stu_name字段的长度改成30

–重命名表明

rename table student to newstu;		-- rename table 原始表名 to 改后的表名；