mysql操作及sql语句

开启关闭mysql，必须在管理员权限下：

Net start mysql

Netstop mysql

Mysql -P 端口号 -h ip –u 用户名–p登录远程数据库

Mysql -u 用户名–p登录本地数据库

Sql语句：

命令语句用;代表结束。不同参数用,分割。字符串的值类型用’’

Sql语句分为：

Ddl：数据定义语言(create drop alter)

Dml:数据操作语言(insert delete update truncate)

Dql:数据查询语言，会返回数据(select show )

管理数据库：

Show databases;显示所有数据库，默认有四个数据库：information schema放的mysql元数据，基础数据。Mysql放的配置信息和用户信息。Performance schema放的日志信息，性能数据。Test是一个空的测试数据库

Create database 名字;创建一个数据库

Default character set utf8;指定该数据库的默认字符集

Dropdatabase名字;删除一个数据库

Show create database 名字;查看一个数据库的创建信息(包括默认字符集)

Alter database 名字default character set gbk;修改一个数据库的默认字符集

Use名字;使用一个数据库

管理表：

Show tables;查看所有表

Desc表名;查看一个表的结构

Create table表名(字段名字段类型,…);创建一个带字段的表(int varchar(长度))

Drop table 表名;删除一个表

Alter table表名rename (to) 表名;修改表名

Alter table表名add (column)字段名varchar(20);增加一个字符串类型的字段

Alter table表名drop (column) 字段名;删除一个字段

Alter table表名modify (column) 字段名类型;修改一个字段的类型

Alter table表名change (column) 字段名新字段名类型;修改字段名

Insert into 表名values(1,’字符串’);按照字段顺序插入数据的所有字段

Insert into 表名(字段名,…) values(值,…)按照指定字段的顺序插入数据

Update表名set 字段名=值;修改表中属于一个字段的所有值

Update表名set 字段名=值where 条件;修改满足条件的数据的字段值

Update表名set 字段名=值,字段名=值;修改多个字段值

Deletefrom表名;删除表中所有数据，表本身还在

Deletefrom 表名where 条件;

Truncate table 表名;删除表的所有数据，truncate不能带条件，truncate既可以删除表的数据也可以删除表的约束，truncate删除的数据不能回滚，delete from删除的数据可以找回。

Select字段名,.. from 表名;查询表内某些字段的数据

Select字段名as ‘别名’,.. from 表名as 别名;查询时指定字段的别名

Select * from 表名;查询表内所有字段的数据

Select字段名,.., ‘常量’ as ‘别名’from 表名;查询时给表加一个有别名的常量列

Select(字段名+字段名) as 别名from 表名;查询时给数值类型的字段合并显示并给个别名

Select distinct 字段名from 表名;查询时去除重复数据

Select distinct (字段名) from 表名;查询时去除重复数据,这里的distinct是一个函数

 

条件语句：

逻辑与或：and、or。

比较条件：<>不等于、between and包头包尾，其他一样。

判空：isnull、is not null、=’’、<>’’。

Like模糊条件：where字段名like 条件。条件是字符串时可以使用替换标记：%任意个字符，_表示一个字符，

 

聚合查询(使用聚合函数)sum、avg、max、min、count

Select sum(字段名) as ‘别名’ from 表名;对字段求和

Select avg(字段名) as ‘别名’ from 表名;对字段求平均数

Select sum(字段名) as ‘别名’ from 表名;对字段求和

Select max(字段名) as ‘别名’ from 表名;求该字段中的最大值

Select min(字段名) as ‘别名’ from 表名;求该字段中的最小值

Select count(字段名) as ‘别名’ from 表名;求该字段有几个值(不包括null)，如果是*，取各字段值的最大值

 

分页查询，limit

Select * from 表名 limit 起始行(从零开始),行数

 

查询排序(默认按插入顺序排序)order by 字段asc/desc，正序和反序

Select * from 表名order by字段(asc),字段desc;按照选定字段的正序显示，不写asc默认正序，可以多个条件排序，第一个排完有重复的用第二个排。

 

分组查询

Select字段名,count(*) from 表名group by 字段名;按照字段名中的相同元素分组，并显示每组数据条数。

 

分组查询后筛选

Select字段名,count(*) from 表名where 条件group by 字段名having 条件;

 

多表查询，确定查询那些表，哪些字段，表与表之间连接条件(条件数量一般是表数量-1)：

内连接查询

select 字段名,…from 表名,…where 表名.字段名=表名.字段名

另一种语法：

Select字段名,…from 表名inner join 表名on 条件

左外连接：

select 字段名,…from 表名 left outer join表名on表名.字段名=表名.字段名，用左面的表匹配右面的表

右外连接和上面相反

 

数据约束：对用户操作表的数据进行约束：

默认值：

作用：当用户对使用默认值的字段不插入值的时候，就是用默认值，插入null就是null

Create table 表名(sex varchar(2) default ‘男’)创建表时默认sex字段为男

非空：

Create table 表名(sex varchar(2) not null)创建表后，插入值时必须赋值sex字段，不能null

唯一：

Create table 表名(id int unique)创建表后，对id赋的值必须是唯一的。可以不赋值，为null

主键：

Create table 表名(id int primary key)创建表后，插入值必须对id赋值，且必须唯一。

自增长：

Create table 表名(id int primary key auto_increment)自增长字段必须是key，不赋值默认1增

Create table 表名(id int(4) zerofill primary key auto_increment)显示4位数，用0填充

外键：

Create table 表名(id int,constraint 外键名称 foreign key(id) references 表名(字段))将id字段设置为外键，参考其他表的字段，如果使用参考字段没有的值就报错。参考字段一般为主键(确保每个值的唯一性)

Create table 表名(id int,constraint 外键名称 foreign key(id) references 表名(字段) on cascade update)对参考表参考字段的修改会直接影响引用表

Create table 表名(id int,constraint 外键名称 foreign key(id) references 表名(字段) on cascade update on delete cascade)修改和删除都会级联，参考表参考字段删除也会在引用表中删除

 

数据库设计：

三大范式，也就是设计原则：

1、  要求表的每个字段必须是不可分割的独立单元，就是每个字段只代表单独含义

2、  在第一范式的基础上，要求每张表只表达一个意思，表的每个字段都和表的主键有依赖

3、  在第二范式的基础上，要求每张表的主键之外的其他字段都和主键有直接决定性的依赖关系

 

存储过程：速度非常快，移植性极差，不同数据库不一样

1、创建存储过程

Delimiter $       //声明标记符

Create procedure 名字(参数)

Begin

         多个sql语句

End $

执行之后就会存在数据库的stored procs中

2、执行删除存储过程

Call名字(参数)就可以调用了

Drop procedure 名字;删除存储过程

3、参数

In：输入参数，携带数据到存储过程中

Out：输出参数，从存储过程中返回数据

Inout：输出输出参数

4、mysql变量

内置变量(全局变量)：mysql数据库的内置变量

Show variables查看所有全局变量

Select @@名字;查看某个全局变量

Set变量名=值;修改变量

会话变量：只存在客户端和数据库的一次连接，连接断开，变量消失

Set @名字=值;定义会话变量

Select @名字;查看某个会话变量

Delimiter $       把结束符号从;更改为$，要不直接就执行sql语句了

Create procedure test(in字段名字段类型,…)

Begin

         Declare 变量名类型default 值

         While 条件do

                   Sql语句;

End while

         If条件then

                   Sql语句;

         Elseif 条件then

                   Sql语句;

         Else

                   Sql语句;

         Endif

End $

Call test(值,@名字…);局部变量用来接收存储过程返回的数据，也可以当做输入数据

 

触发器：

1、创建触发器

Create trigger 名字after insert/update/delete on 表名for each row

         Sql语句;

执行之后就会存储在数据库的触发器文件夹中

2、触发触发器

 

数据库权限：

Select password(‘字符串’);对该用户的密码进行md5算法加密，单向加密，只能加密不能解密。

在mysql库中存放用户信息。

Update user set password=password(‘密码’) where user=xxx;修改用户密码

Grant权限on 数据表.表to ‘账户名’@’账户类型’ identified by ‘密码’;分配权限用户

权限：select、insert、delete、update、drop、create、all

账户类型：localhost、ip地址、%(本地远程都可以登录)

 

数据库备份与恢复

备份：Mysqldump –u root –p 数据库名>文件路径

恢复：Mysql –u root –p 数据库名<文件路径

 

事务：

原子性：事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性：事务必须使数据库从一个一致性状态变换到另一个一致性状态。

隔离性：多个用户访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据干扰，多个并发事务之间要相互隔离。

持久性：一个事务一旦被提交，它对数据库的影响就是永久性的

脏读：读取到其他用户修改后还没提交的数据，该数据是没有价值的

重复读：两次读取中间，已经被其他用户修改并提交了

虚读：和重复读差不多？

四种隔离级别：

Serializable：都可避免

Repeatable read：避免脏读、重复读(mysql默认的)

Read commited：避免脏读

Read uncommitted：最低级别，什么都不可避免

Set transaction isolation level设置事务隔离级别

Select @@tx_isolation查询当前事务隔离级别

Start transaction开启事务

Commit提交事务

Rollback事务回滚

共享锁：一个事务开启时使用了共享锁，其他事务也可以设置共享锁，只能读数据，插入数据的话就会阻塞，直到其它事务都提交了

Select * from 表名lock in share mode

排它锁：一个事务开启时使用了排它锁，其他事务任何操作都只能等待

Select * from 表名for update