MySql的简单使用

MySql的简单使用

1.登录mysql

mysql -u root -p

SQL语言分类

• 数据定义语言：简称【DDL】(Data Definition Language)，用来定义数据库对象：数据库，表，列等。关键字：create，alter，drop等- 数据操作语言：简称【DML】(Data Manipulation Language)，用来对数据库中表的记录进行更新。关键字：insert，delete，update等- 数据控制语言：简称【DCL】(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用户；关键字：grant等- 数据查询语言：简称【DQL】(Data Query Language)，用来查询数据库中表的记录。关键字：select，from，where等
  如果要了解标准的内容，比较推荐的方法是【泛读SQL92】（因为它涉及了SQL最基础和最核心的一些内容），然
  后增量式的阅读其他标准。

DDL语句
数据库操作：database
创建数据库
create database 数据库名;create database 数据库名 character set 字符集;
查看数据库
查看数据库服务器中的所有的数据库:
show databases;
查看某个数据库的定义的信息:
show create database 数据库名;
删除数据库（慎用）
drop database 数据库名称;
其他数据库操作命令
切换数据库：
use 数据库名;
查看正在使用的数据库:
select database();

表操作：table
创建表
create table 表名( 字段名 类型(长度) 约束, 字段名 类型(长度) 约束);
比如创建一个用户表

单表约束：
主键约束：primary key- 唯一约束：unique- 非空约束：not null
主键约束 = 唯一约束 + 非空约束
查看表
查看数据库中的所有表：
show tables;
查看表结构：
desc 表名;
删除表
drop table 表名;
修改表
alter table 表名 add 列名 类型(长度) 约束; --修改表添加列.alter table 表名 modify 列名 类型(长度) 约束; --修改表修改列的类型长度及约束.alter table 表名 change 旧列名 新列名 类型(长度) 约束; --修改表修改列名.alter table 表名 drop 列名; --修改表删除列.rename table 表名 to 新表名; --修改表名alter table 表名 character set 字符集; --修改表的字符集

DML语句
插入记录：insert
insert into 表 (列名1,列名2,列名3…) values (值1,值2,值3…); – 向表中插入某些列insert into 表 values (值1,值2,值3…); --向表中插入所有列insert into 表 (列名1,列名2,列名3…) values select (列名1,列名2,列名3…) from 表insert into 表 values select * from 表

注意：1. 列名数与values后面的值的个数相等2. 列的顺序与插入的值得顺序一致3. 列名的类型与插入的值要一致.4. 插入值得时候不能超过最大长度.5. 值如果是字符串或者日期需要加引号’’ ，一般是单引号

注意插入的时候如果没有申明列，那么需要插入每一列，不然会报错：column count doesn`t match value count at row xx.

更新记录：update
update 表名 set 字段名=值,字段名=值;update 表名 set 字段名=值,字段名=值 where 条件;
注意：

1. 列名的类型与修改的值要一致.
2. 修改值得时候不能超过最大长度.
3. 值如果是字符串或者日期需要加’’

删除记录：delete
delete from 表名 [where 条件];
删除表中所有记录使用【delete from 表名】，还是用【truncate table 表名】？
删除方式：- delete ：一条一条删除，不清空auto_increment记录数。- truncate ：直接将表删除，重新建表，auto_increment将置为零，从新开始。
DQL语句

DQL语法顺序
完整DQL语法顺序
SELECT DISTINCT < select_list >FROM < left_table > < join_type >JOIN < right_table > ON < join_condition >WHERE < where_condition >GROUP BY < group_by_list >HAVING < having_condition >ORDER BY < order_by_condition >LIMIT < limit_number >
简单查询
查询所有的商品
select * from product;

• 表示表的所有字段，也可以指定具体字段，甚至可以给字段取别名
  select pname ,price from product;
  别名查询，使用的as关键字，as可以省略的
  select * from product as p;
  去掉重复值
  select distinct price from product;
  运算查询
  select pname,price+10 from product;
  数据库的运算查询是很消耗时间的，不建议在数据库这样操作，应该将数据在后台程序进行操作，数据库只处理数据，不应该处理逻辑。

条件查询
查询商品价格>60元的所有的商品信息
select * from product where price > 60;
符号说明

,<,=,>=,<=,<>like 使用占位符 _ 和 % _代表一个字符 %代表任意个字符. select * from product where pname like ‘%新%’;in在某个范围中获得值（exists）. select * from product where pid in (2,5,8);

排序
查询所有的商品，按价格进行排序.(asc-升序,desc-降序)
默认就是asc
select * from product order by price;
聚合函数（组函数）
特点：只对单列进行操作
常用的聚合函数
sum()：求某一列的和avg()：求某一列的平均值max()：求某一列的最大值min()：求某一列的最小值count()：求某一列的元素个数
获得所有商品的价格的总和：
select sum(price) from product;
获得所有商品的个数
select count() from product;
分组
根据cid字段分组，分组后统计商品的个数
select cid,count() from product group by cid;
根据cid分组，分组统计每组商品的平均价格，并且平均价格> 60;
select cid,avg(price) from product group by cid having avg(price)>60;
注意事项
select语句中的列（非聚合函数列），必须出现在group by子句中group by子句中的列，不一定要出现在select语句中聚合函数只能出现select语句中或者having语句中，一定不能出现在where语句中。
分页查询
分页分为逻辑分页和物理分页
逻辑分页：将数据库中的数据查询到内存之后再进行分页。物理分页：通过LIMIT关键字，直接在数据库中进行分页，最终返回的数据，只是分页后的数据。
lIMIT 关键字不是 SQL92 标准提出的关键字，它是 MySQL 独有的语法。
通过 limit 关键字， MySQL 实现了物理分页
SELECT * FROM table LIMIT [offset,] rows
子查询
子查询允许把一个查询嵌套在另一个查询当中。子查询，又叫内部查询，相对于内部查询，包含内部查询的就称为外部查询。子查询可以包含普通select可以包括的任何子句，比如：distinct、 group by、order by、limit、join和union等；但是对应的外部查询必须是以下语句之一：select、insert、update、delete
其他查询语句
union 集合的并集（不包含重复记录）unionall 集合的并集（包含重复记录

SQL解析顺序
SELECT DISTINCT < select_list >FROM < left_table > < join_type >JOIN < right_table > ON < join_condition >WHERE < where_condition >GROUP BY < group_by_list >HAVING < having_condition >ORDER BY < order_by_condition >LIMIT < limit_number >
然而它的执行顺序是这样的
1 FROM <left_table>2 ON <join_condition>3 <join_type> JOIN <right_table> 第二步和第三步会循环执行4 WHERE <where_condition> 第四步会循环执行，多个条件的执行顺序是从左往右的。5 GROUP BY <group_by_list>6 HAVING <having_condition>7 SELECT 分组之后才会执行SELECT8 DISTINCT <select_list>9 ORDER BY <order_by_condition>10 LIMIT <limit_number> 前9步都是SQL92标准语法。limit是MySQL的独有语法。
MySQL的sql解析
首先看一下示例语句
SELECT DISTINCT < select_list >FROM < left_table > < join_type >JOIN < right_table > ON < join_condition >WHERE < where_condition >GROUP BY < group_by_list >HAVING < having_condition >ORDER BY < order_by_condition >LIMIT < limit_number >
然而它的执行顺序是这样的
1 FROM <left_table>2 ON <join_condition>3 <join_type> JOIN <right_table> 第二步和第三步会循环执行4 WHERE <where_condition> 第四步会循环执行，多个条件的执行顺序是从左往右的。5 GROUP BY <group_by_list>6 HAVING <having_condition>7 SELECT 分组之后才会执行SELECT8 DISTINCT <select_list>9 ORDER BY <order_by_condition>10 LIMIT <limit_number> 前9步都是SQL92标准语法。limit是MySQL的独有语法
来看一个例子
假设有表1和表2
table1

table2

1、from
2个表联合查询得到他们的笛卡尔积CROSS JOIN，产生 虚表VT1

2、ON过滤
对 虚表VT1 进行ON筛选，只有那些符合的行才会被记录在虚表VT2中。
注意：这里因为语法限制，使用了’WHERE’代替，从中读者也可以感受到两者之间微妙的关系；

3.OUTER JOIN添加外部列
如果指定了 OUTER JOIN（比如left join、 right join） ，那么 保留表中未匹配的行 就会作为外部行 添加 到 虚
拟表VT2 中，产生 虚拟表VT3 。
如果FROM子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3
这三个步骤，一直到处理完所有的表为止

4.WHERE
对 虚拟表VT3 进行WHERE条件过滤。只有符合的记录才会被插入到 虚拟表VT4 中。

5.GROUP BY
根据group by子句中的列，对VT4中的记录进行分组操作，产生 虚拟表VT5 。
注意：
其后处理过程的语句，如SELECT,HAVING，所用到的列必须包含在GROUP BY中。对于没有出现的，得用聚合函
数；
原因：
GROUP BY改变了对表的引用，将其转换为新的引用方式，能够对其进行下一级逻辑操作的列会减少；

6.HAVING
对 虚拟表VT5 应用having过滤，只有符合的记录才会被 插入到 虚拟表VT6 中。

7.SELECT
这个子句对SELECT子句中的元素进行处理，生成VT5表。
(5-J1)计算表达式 计算SELECT 子句中的表达式，生成VT5-J1
8.DISTINCT
寻找VT5-1中的重复列，并删掉，生成VT5-J2
如果在查询中指定了DISTINCT子句，则会创建一张内存临时表（如果内存放不下，就需要存放在硬盘了）。这张
临时表的表结构和上一步产生的虚拟表VT5是一样的，不同的是对进行DISTINCT操作的列增加了一个唯一索引，以
此来除重复数据

9.ORDER BY
从 VT5-J2 中的表中，根据ORDER BY 子句的条件对结果进行排序，生成VT6表。
注意：
唯一可使用SELECT中别名的地方

10.LIMIT（MySQL特有）
LIMIT子句从上一步得到的 VT6虚拟表 中选出从指定位置开始的指定行数据
注意：
offset 和 rows 的正负带来的影响；
当偏移量很大时效率是很低的，可以这么做：
采用子查询的方式优化 ，在子查询里先从索引获取到最大id，然后倒序排，再取N行结果集
采用INNER JOIN优化 ，JOIN子句里也优先从索引获取ID列表，然后直接关联查询获得最终结果

解析顺序总结

流程分析

1. FROM（将最近的两张表，进行笛卡尔积）—VT12. ON（将VT1按照它的条件进行过滤）—VT23. LEFT JOIN（保留左表的记录）—VT34. WHERE（过滤VT3中的记录）–VT4…VTn5. GROUP BY（对VT4的记录进行分组）—VT56. HAVING（对VT5中的记录进行过滤）—VT67. SELECT（对VT6中的记录，选取指定的列）–VT78. ORDER BY（对VT7的记录进行排序）–VT89. LIMIT（对排序之后的值进行分页）–MySQL特有的语法
   流程说明：
   单表查询：根据 WHERE 条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据SELECT 的选择列选择相应的列进行返回最终结果。 两表连接查询：对两表求积（笛卡尔积）并用 ON 条件和连接连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据 SELECT 指定的列返回查询结果。 笛卡尔积：行相乘、列相加 多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。
   MySQL表连接
   有3种：
   JOIN 按照功能大致分为如下三类：
   CROSS JOIN （交叉连接）INNER JOIN （内连接或等值连接）。OUTER JOIN （外连接）
   交叉连接CROSS JOIN
   交叉连接也叫笛卡尔积连接。笛卡尔积是指在数学中，两个集合 X 和 Y 的笛卡尓积（ Cartesian product ），
   又称直积，表示为 X*Y ，第一个对象是 X 的成员而第二个对象是 Y 的所有可能有序对的其中一个成员。
   隐式交叉连接
   SELECT * FROM A, B
   显式交叉连接
   SELECT * FROM A CROSS JOIN B
   内连接INNER JOIN
   内连接也叫等值连接，内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。
   隐式内连接
   SELECT * FROM A,B WHERE A.id = B.id
   显式内连接
   SELECT * FROM A INNER JOIN B ON A.id = B.id
   外连接
   外联接可以是左向外联接、右向外联接或完整外部联接。也就是说外连接又分为：左外连接、右外连接、全外连接。外连接需要有主表或者保留表的概念
   左外连接LEFT JOIN 或者 LEFT OUTER JOIN
   SELECT * FROM A LEFT JOIN B ON A.id = B.id
   右外连接:RIGHT JOIN 或者 RIGHT OUTER JOIN
   SELECT * FROM A RIGHT JOIN B ON A.id = B.id
   外连接总结：
   通过业务需求，分析主从表
   如果使用 LEFT JOIN ，则主表在它左边
   如果使用 RIGHT JOIN ，则主表在它右边
   查询结果以主表为主，从表记录匹配不到，则补 null
   MySQL建表语句
   实例：　　
   CREATE TABLE user2 ( id int(11) NOT NULL AUTO_INCREMENT, username varchar(50) DEFAULT NULL, sex varchar(5) DEFAULT NULL, address varchar(100) DEFAULT NULL, birthday datetime NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
   语法：
   create table 表名( 字段名 类型(长度) 约束, 字段名 类型(长度) 约束);
   如果是新的数据表，创建的时候默认值
   ENGINE=InnoDBAUTO_INCREMENT=1CHARSET=latin1
   MySQL锁
   按照锁的粒度来说，MySQL主要包含三种类型（级别）的锁定机制：

• 全局锁：锁的是整个database。由MySQL的SQL layer层实现的- 表级锁：锁的是某个table。由MySQL的SQL layer层实现的- 行级锁：锁的是某行数据，也可能锁定行之间的间隙。由某些存储引擎实现，比如InnoDB。
  　　
  表级锁和行级锁的区别：
  　　
  表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
  MySQL表级锁
  由MySQL SQL layer层实现，一种是表锁。一种是元数据锁（meta data lock，MDL)。
  MySQL 实现的表级锁定的争用状态变量：
  　　
  show status like ‘table%’;

• table_locks_immediate：产生表级锁定的次数；- table_locks_waited：出现表级锁定争用而发生等待的次数；
  表锁有两种表现形式：
  　　
  表共享读锁（Table Read Lock）表独占写锁（Table Write Lock）
  手动增加表锁
  　　
  lock table 表名称 read(write),表名称2 read(write)，其他;
  查看表锁情况
  　　
  show open tables;
  删除表锁
  　　
  unlock tables;
  实例：

CREATE TABLE mylock (id int(11) NOT NULL AUTO_INCREMENT,NAME varchar(20) DEFAULT NULL,PRIMARY KEY (id));INSERT INTO mylock (id,NAME) VALUES (1, ‘a’);INSERT INTO mylock (id,NAME) VALUES (2, ‘b’);INSERT INTO mylock (id,NAME) VALUES (3, ‘c’);INSERT INTO mylock (id,NAME) VALUES (4, ‘d’);
MySQL操作语句
SQL分类
DDL （Data Definition Languages）语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括create、drop、alter等。
DML （Data Manipulation Language）语句：数据操纵语言，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括insert、delete、update和select等。
DCL （Data Control Language）语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke等。

数据定义语句ddl：
#mysql注释#创建表create table user( id int PRIMARY key auto_increment, userName varchar(50), userId varchar(50), gender varchar(5) default ‘男’, birthday varchar(20), address varchar(100)); #查看表desc user; #修改表#修改列类型#注意：不是任何情况下都可以去修改的，#只有当字段只包含空值时才可以修改。alter table user modify gender varchar(4);#增加列alter table user add tel varchar(50);#删除列alter table user drop tel;alter table user drop column tel;#列改名alter table user change userName emp_Name varchar(18);#更改表名alter table user rename emp;rename table emp to user;
创建表
#mysql注释#创建表create table user( id int PRIMARY key auto_increment, userName varchar(50), userId varchar(50), gender varchar(5) default ‘男’, birthday varchar(20), address varchar(100));

查看表
#查看表desc user;

删除表
　　
#删除表drop table user;

修改表
　　
#修改表#修改列类型#注意：不是任何情况下都可以去修改的，#只有当字段只包含空值时才可以修改。alter table user modify gender varchar(4);#增加列alter table user add tel varchar(50);#删除列alter table user drop tel;alter table user drop column tel;#列改名alter table user change userName emp_Name varchar(18);#更改表名alter table user rename emp;rename table emp to user;
注意：如果删除的列不存在，会报错的