Mysql基础

Mysql基础

一.常用命令
1.连接数据库：
mysql -u 用户名 -p + 密码 明文连接
mysql -u用户名 -p + 回车输密码 密码形式连接数据库
2.创建数据库：
create database 数据库名称(此种创建方式若已存在将会报错)
create database if not exists 数据库名称；(如果存在只会报警告)
3.删除数据库
drop database 数据库名称
drop database if exists数据库名称；
4.查看所有数据库
show database
5.使用数据库
use 数据库名称
6.退出mysql命令提示行
exit或者quit
二.创建表
前提：必须先使用use命令使用一个数据库
create table [if not exists]表名(
列名 类型(长度) + 其他修饰符，比如[不能为空，主键，外键，索引，默认值，注释等等]
)[ENGINE = InnoDB DEFAULT CHAREST=utf8];引擎和编码为可选项，不写表示默认值
desc 表明 ： 显示表结构
show create table 表名：显示创建表语句
三.字段类型和属性
1.字段类型


2.字段属性
not null：不能为空
unsigned：无符号(数值不能为负数)
zerofill：0填充
auto_increment：自动增长
comment：注释
default：默认值
四.修改表内容
修改表明：alter table 表名 rename as 新表名
修改字段属性：alter table 表名 modify 字段名 属性
添加新字段：alter table 表名add字段名 属性
修改字段名和字段属性：alter table 表名 chage 旧字段名 新字段名 属性
删除字段：alter table 表名 drop 字段名
五.删除表
drop table 表名
六.数据库引擎区别
MyISAM 和InnoDB

7.主键，外键
主键索引，主键约束，唯一不能重复不能为空，一个人表最多只能有一个，用于方便管理，
外键，被约束为外键的列，通常是主键索引，或者是唯一索引，否则不能被关联为外键，例如一个学生表一个班级表，其中学生表中的班级信息用外键约束链接到班级表

学生表：

create table student(
	stuid int(3) primary key auto_increment,
	stuage int(3) ,
	city varchar(20),
	gid int(3),
	email varchar(50),
	borndate datetime,
	info varchar(100)
)

班级表：

create table grade(
	gradeid int(3) primary key auto_increment,
	gradename varchar(20) not null,
	seatcount int(3) not null,
	classroomno varchar(20) not null
)

外键约束：alter table student add constraint FK_gid foreign key (gid) references grade(gradeid);
创建外键时会自动创建一个外键索引，删除外键是索引也要删除
删除外键：alter table person drop foreign key FK_cid;
删除索引：alter table person drop index FK_cid;
8.数据的增删改
添加数据：insert into
insert into student(sid,sname,saddress,sex,sborndate)values(1,‘广坤’，‘铁岭’,‘男’,‘2010-01-01 10:00:00’)；
当主键是自增的时候我们可以不写ID：
insert into stu (sname,saddress,sex,sborndate) values(‘刘能’,‘铁岭’,‘男’,‘2010-01-01 10:00:00’);
当所有的列都添加内容，可以直接写值，不写列名
insert into stu values (3,‘大拿’,‘铁岭’,‘男’,‘2010-01-01 10:00:00’);
添加多条语句的时候可以直接在values后用逗号隔开
insert into stu(sname,saddress,sex,sborndate)
values
(‘广坤1’,‘铁岭’,‘男’,‘2010-01-01 10:00:00’),
(‘广坤2’,‘铁岭’,‘男’,‘2010-01-01 10:00:00’),
(‘广坤3’,‘铁岭’,‘男’,‘2010-01-01 10:00:00’),
(‘广坤4’,‘铁岭’,‘男’,‘2010-01-01 10:00:00’),
(‘广坤5’,‘铁岭’,‘男’,‘2010-01-01 10:00:00’);
添加数据在列名书写的情况下对应列名就好，不一定要按顺序：
insert into stu (saddress,sex,sborndate,sname) values(‘铁岭’,‘男’,‘2010-01-01 10:00:00’,‘刘能1’);
查询语句，查询整表：
select * from 表名 ；
修改数据 update + 表名
当不添加约束条件时会使整列都变成我们修改的信息例如：
update stu set sname = ‘富贵’;
不进行限定的修改会把sname这一列都改为富贵；
根据主键修改名字,修改sid为1 的sname为小沈阳：
update stu set sname = “小沈阳” where sid=1;
根据名字修改内容，修改名字为小沈阳的人其地址为象牙山：
update stu set saddress = “象牙山” where = sname = “小沈阳”；
根据一个信息修改多个信息，根据sid修改名字和地址
update stu set saddress = “象牙山” ， sname = “大拿” where sid = 2;
根据多个条件修改内容可以使用and或者&& 例如根据名字和id修改名字
update stu set sname = “赵武” where sid = 4 and sanme = “富贵”;
删除数据，不添加约束条件是和修改一样将对所有找到的数据进行删除
delete from stu;
根据id删除数据
delete from stu where sid = 10;
删除表数据并清空索引
truncate stu;
删除整个表，不保留表结构
drop stu;

删除 drop(删除整个表，不保留表结构) > truncate (删除整个表数据，保留表结构，清空索引)> delete(删除数据 保留索引值)

查询语句：
查询中的表示所有列
使用星号将会查询所有的列数据 但是我们通常只会需要其中几个列的内容 所以我们加上列名 单独查询
查询所有学生的名字：
select sname from stu;
查询名字和性别
select sname, sex from stu;
查询多项时用逗号隔开
select sname ,sex ,sborndate from stu;
查询数据并且给sname和sex取别名 as 关键字可以省略
select sname as ‘学生名字’,sex as ‘性别’ from stu;
给表取别名，不要指定中文名字
select sname as ‘学生名字’ from stu as s;
根据主键编号，查询学生名字和性别
select sname , sex from stu as s where s.sid = 2;
根据年龄去重复
select distinct age, sname from stu;
and表示多个条件成立，or表示有一个条件成立即可
select * from stu where saddress = ‘铁岭’ and age = 20;
select * from stu where sname = ‘广坤’ or age = 20;
not null表示查询条件非空
select * from stu where semail is null;
between a and b 表示查询条件在a和b之间
select * from stu where age between 22 and 25;
like表示模糊查询，其中%表示多个字符可以为0，_表示一个字符
select * from stu where sname like ‘%赵%’;
in表示查询条件在某集合中，比如集合中先进行查询，得到一个结果，然后用IN进行根据条件查询
9.多表查询
等值连接查询，两表或者多表进行等值连接查询，取交集
select p.name ,c.canme from person as p, city as c where p.city= c.cid;
内连接 表A inner join 表B on A.field1 = B.filed1
select * from person inner join city on person.city = city.cid;
外连接查询，左外连，以左表为基础，右边无对应数据，显示为空，右连接，以右表为基础，若无对应数据左边显示为空
select * from person left join city on person.city = city.cid;
select * from person right join city on person.city = city.cid;
10.授权
创建用户 并且授权 可以单独授予 增 删 改 查 这些权限，关键字为grant
create user ‘zs’;
grant select on zz206. to ‘zs’;
grant update on zz206.* to ‘zs’;
grant delete on zz206.* to ‘zs’;
grant insert on zz206.* to ‘zs’;
grant all on zz206.* to ‘zs’;
create user ‘myroot’ identified by ‘zs8989’;
grant all on zz206.* to ‘myroot’;
11.事务
事务特征：atomicity 原子性 一个事务是一个整体 可以包含多条sql语句
consistency 一致性 要么都执行 要么都不执行 不可拆分 通过事务执行sql 必须保证一致性
isolation 隔离性 一个事务在执行过程中对未提交的数据 外界不可访问
durability 持久性 通过事务最终提交的数据 将持久化到数据库 不可改变
支付宝转账为例子：

create table alipay(
	aliid int(3) primary key auto_increment,
	alicash decimal(10,2) not null
)

create table bank(
	bid int(3) primary key auto_increment,
	bcash decimal(10,2) not null
)

先将自动提交关闭
set autocommit = 0;
开启一个事物
start transaction;
模拟支付宝转账到银行账户
update alipay set alicash = alicash - 2000;
转账以后银行账户增加2000
update bank set bcash = bcash + 2000;
select * from bank;
selcet * from alipay;
在事物结束以前只能在当前查询页面看到信息变化了，外部查看数据未变化
rollcack把事务回滚到执行之前
commit提交永久化，之后不能进行回滚操作，外部可进行查看
开启自动提交
set autocommit = 1；
12.视图
视图相当于一个虚拟表 用于简化我们复杂的sql查询
create view stu_view as select * from stu;
select sname from stu_view;
修改视图中的结果内容
create or replace view stu_view as select sname from stu;
修改视图
alter view stu_view as select * from stu;
删除视图
drop view stu_view;
合并结果集 union 和 union all
union 不会保留重复的内容
union all 会保留重复的内容
合并的结果 两张表中 必须列数对应
select * from city union select * from dep;
select * from city union all select * from dep;
select * from stu union select * from dep;