Mysql学习记录（一）基本操作

连接服务器

mysql -uusername -ppassword
举例:
mysql -uroot -p123456

Mysql服务启动

1.手动
2.cmd --> services.msc 打开服务的窗口
3.使用管理员打开cmd

• net start mysql : 启动MySQL的服务
• net stop mysql : 关闭MySQL服务

SQL分类

1. DDL（Data Definition Language）数据定义语言
   用来定义数据库对象：数据库，表，列等。关键字：create,drop,alter
2. DML(Data Manipulation Language)数据操作语言
   用来对数据库表中的数据进行增删改。关键字：insert、delete、update等
3. DQL(Data Query Language)数据查询语言
   用来查询数据库中表的记录（数据）。关键字：select,where 等
4. DCL(Data Control Language)数据控制语言（了解）
   用来定义数据库中的访问权限和安全级别，以及创建用户。关键字：GRANT，REVOKE等

CRUD的概念

C（create）创建
R（Retrieve）查询
U（Update）修改
D（Delete）删除

选择一个库 注意加;

use 库名;
举例:

use web01;  

显示所有库

show databases;  

创建库

create datebase 库名 charset 字符集;  

举例:

create database web01 charset utf8;  
create database if not exists web01 charset utf8;  

//创建之前先查询是否存在

删除库

drop database 库名;  

举例：

drop database web01;  

判断数据库是否存在，存在再删除

drop database if exists web01;

数据库改名

数据库不能改名 表/列可以改名

查看当前正在使用的数据库

select database();

添加

1.1 添加新表

	create table table_name(
		COLUMN1 DATA_TYPE [NULL|NOT NULL] default type,
	 	COLUMN2 DATA_TYPE [NULL|NOT NULL] default type
	)
	举例;
	create table user(
		id int key auto_increment,
		username varchar(16) not null default '',
		passwd varchar(16) not null default '',
		salary decimal(6,2) not null default 0.00,
		waikuai smallint not null default 0
	);
	key auto_increment  值自动填充

1.2 给表格添加数据

	insert into tablename 
	(valuename)
	values
	(value)
	举例：
	insert into user
	(id,username,passwd,salary,waikuai)
	values
	(1,'zhangsan','123',4554.78,200);
	
	如果插入所有列的时候不用加valuename
	举例：
	insert into user values
	(1,'zhangsan','123',4554.78,200);
	
	插入多行数据
	insert into user1 
	(username,passwd,salary,waikuai)
	values
	('大黑','799',613.58,55),
	('大黄','739',623.58,55),
	('大黑','779',683.58,15);

1.3 复制表

create table 表名 like 要复制的表名;

删除

2.1 删除表中数据

truncate tablename;

举例：

truncate user;
delete from user;

truncate 和 delete 的区别：
truncate 相当于删表再重建一张同样结构的表，操作后得到一张全新表；
delete 是从删除数据行的层面来操作的，比如删除前表中id最大是3，那么再次添加会从4开始；

更新

3.1 修改表，列名 类型

rename table oldtablename to newtablename;

举例：两种修改方式

rename table user to user1;
alter table user1 rename to user;

修改类型

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

3.2 修改表的字符集

alter table 表名 character set 字符集名称

查询

4.1 查询所有

select column from tablename where expression;

expression:表达式
举例：
select username,salary from user where id = 3;

查询年龄在20-30岁之间的人
select * from user where age>=20 && age<=30; //不推荐写法
select * from user where age>=20 and age<=30;
select * from user where age between 20 and 30;
查询年龄为 18 22 25岁的人
select * from user where age=18 or age=22 or age=25;
select * from user where age in(18,22,25);
查询年龄不为20岁的人
select * from user where age !=20;
select * from user where age <> 20;
查询英语成绩为null的人
select * from user where english is null;
查询英语成绩不为null的人
select * from user where english is not null;

模糊查找：
#查找名字首字母为小的人
select * from user where name like ‘小%’；
#查找名字第二个字为黑的人
select * from user where name like ‘_黑%’；
查询姓名是三个字的人
select * from user where name like '___';
查询姓名中包含马的人
select * from user where name like '%马%';

4.2 查询最后一个插入的值

select last_insert_id();

4.3 查询并去除重复数据

select distinct condition from tablename;

举例：

select distinct passwd from user;

4.4 查询并去除NULL

计算数学和英语的总和（英语有个值为null）

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

ifnull(expre1 , expre2) 如果expre1 为null ,那么expre2

4.5 起别名

select condition1 空格或者as ,condition2 空格或者as from 表名;

4.6 排序查询

-- 默认按照升序排序
select * from user order by math;
select * from user order by math asc;
-- 降序排序
select * from user order by math desc;
-- 多个排序条件, 只有前面的条件有重复，才会判断后面的条件
select * from user order by math asc,english asc ;

4.7 聚合函数

将一列数据作为一个整体，进行纵向的计算
计算过程中会排除null数据

count min max sum avg
如果想使用带NULL的列运算(建议尽量使用非空列计算count)

select count(ifnull(english,0)) from user;

4.8 分组查询

语法：group by
注意：

分组之后查询的字段：分组字段，聚合函数

-- 按性别分组，查询这两组的平均数学成绩
-- sex 分组字段
select sex , avg(math) from user group by sex;
-- 在上面基础上，数学分数低于70，不参与分组
select sex , avg(math) from user where math > 70 group by sex;
-- 在上面基础上，分组之后人数要大于两个人
select sex , avg(math) from user where math > 70 group by sex having count(id) >2;

where 和 having的区别？
where在分组之前，如果不满足条件，则不参与分组，
having在分组之后，如果不满足条件，则不查询。
where后面不能加聚合函数，having可以加

4.9 分页查询

Mysql分页查询是一个“方言”，就是说limit关键字只能在MySQL中使用

-- 每页三条记录
SELECT * FROM products LIMIT 0,3; -- 第一页
SELECT * FROM products LIMIT 3,3; -- 第二页
公式 ：每页开始的索引=（当前页码-1）*每页条数

约束

• 概念：对表中的数据进行限定，保证数据的正确性，有效性和完整性

1、 主键约束 primary limit

注意：
a) 含义：非空且唯一
b) 一张表只能有一个字段为主键
c) 主键就是表中记录的唯一标识
创建表时，添加主键标识：

create table stu(
id int primary key, --添加主键约束
name varchar(20) not null  -- name 为非空
);

删除主键：

 alter table user drop primary key;

创建完表后，添加主键；

alter table user modify id int primary key;

删除自动增长

alter table user modify id int;

添加自动增长

alter table user modify id int auto_increment;

2、 非空约束 not null

创建表时添加约束：

create table stu(
id int,
name varchar(20) not null  -- name 为非空
);

创建完成后添加约束：

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

3、 唯一约束;unique 不允许重复

创建时添加唯一约束：

create table stu(
id int,
name varchar(20) unique  -- name 唯一约束
);

注意：MySQL，唯一约束限定的列的值可以有多个null
创建完成后添加约束：

alter table user modify name varchar(20) unique;

删除唯一约束：

alter table user drop index name;

4、 外键约束：foreign key 让表与表产生关系,保证数据的正确性

1、 在创建表时添加外键：
语法：

create table 表名(
...
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称（主表列名称）
)；

2、 删除外键

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

3、 创建表之后，添加外键

alter table 表名 add  foreign key (外键列名称) references 主表名称（主表列名称）

若需重命名外键名称

alter table 表名 add constraint 外键名称 foreign key (外键列名称) references 主表名称（主表列名称）

并且设置级联更新 ，设置级联删除

alter table 表名 add constraint 外键名称 foreign key (外键列名称) references 主表名称（主表列名称） on update cascade on delete cascade;

数据库的备份和还原：

备份：mysqldump -u用户名 -p密码 数据库名 > 路径
举例：

mysqldump -uroot -p123456 web01 > D://a.sql

还原：创建数据库
使用数据库
source 路径
举例：

source D://a.sql

多表关系

联合主键

create table tab_fav(
	rid int, -- 线路id
	DATE DATETIME,
	uid int, -- 用户id
	-- 创建联合主键
	 primary key(rid,uid),
	 foreign key(rid) references tab_route(rid),
	 foreign key(uid) references tab_user(uid)
);