Mysql常用语句
第一招、mysql服务的启动和停止
net stop mysqlnet start mysql第二招、登陆mysql语法如下： mysql -u用户名 -p用户密码键入命令mysql -uroot -p， 回车后提示你输入密码，输入12345，然后回车即可进入到mysql中了，mysql的提示符是：mysql>注意，如果是连接到另外的机器上，则需要加入一个参数-h机器IP第三招、增加新用户格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码"如，增加一个用户user1密码为password1，让其可以在本机上登录， 并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入mysql，然后键入以下命令：grant select,insert,update,delete on *.* to user1@localhost Identified by "password1";如果希望该用户能够在任何机器上登陆mysql，则将localhost改为"%"。如果你不想user1有密码，可以再打一个命令将密码去掉。grant select,insert,update,delete on mydb.* to user1@localhost identified by "";第四招： 操作数据库登录到mysql中，然后在mysql的提示符下运行下列命令，每个命令以分号结束。1、 显示数据库列表。show databases;缺省有两个数据库：mysql和test。 mysql库存放着mysql的系统和用户权限信息，我们改密码和新增用户，实际上就是对这个库进行操作。2、 显示库中的数据表：use 库名;show tables;3、 显示数据表的结构：describe 表名; desc 表名;4、 建库与删库：create database 库名;drop database 库名;5、 建表与删表：use 库名;create table 表名(字段列表); 例如：CREATE TABLE user (name VARCHAR(20), sex CHAR(1));drop table 表名;6、 清空表中记录：delete from 表名 where ... ; 没有where会删除所用的记录。7、 显示表中的记录：select * from 表名;8、往表中加入记录
insert into user values ("hyq","M"); 此法必须列出所有字段的值。（按顺序）insert into user (name, sex) values ("hyq","M");9、更新表中数据
update user set sex="f" where name='hyq';10、修改表名
ALTER TABLE 表名 RENAME [TO|AS] 新表名ALTER TABLE user10 RENAME TO user11; 或RENAME TABLE 表名 TO 新表名； 这里面的TO不可以省略11、修改字段（字段就是列名）
修改字段类型alter table 表名 modify column 字段 varchar(25);修改字段名alter table 表名 change 字段名 新字段名 类型 ; //一定要带类型，因为原来字段的所有属性清空了。alter table tablename change id id int auto_increment primary key;增加字段alter table 表名 add 字段名 类型;删除字段alter table 表名 drop 字段名;删除主键alter table 表名 drop primary key; //这是该字段没有设置auto_increment 的时候，可以用。alter table 表名 modify id int, drop primary key; //当字段id设置了auto_increment 时，删除主键第五招、导出和导入数据
1. 导出数据：mysqldump --opt test > mysql.test即将数据库test数据库导出到mysql.test文件，后者是一个文本文件如：mysqldump -u root -p123456 --databases dbname > mysql.dbname就是把数据库dbname导出到文件mysql.dbname中。如：mysqldump -uroot -p123456 数据库名 表名 > test.sql (即文件名，路径是当前路径)2. 导入数据:
mysqlimport -u root -p123456 < mysql.dbname。不用解释了吧。3. 将文本数据导入数据库:文本数据的字段数据之间用tab键隔开。use test;load data local infile "文件名" into table 表名;4.导入.sql文件命令(例如D:/mysql.sql)
mysql>use database;mysql>source d:/mysql.sql;5、退出MYSQL命令：quit / exit (回车) ====================================DML 数据操作语言对表记录的增删改插入：INSERT INTO 表名（列1，列2，...） VALUES(值1，值2，...)SELECT * FROM 表名；——显示更新：UPDATE 表名 SET 列1=列值1，列2=列值2，WHERE 条件； 注意，不加where会修改列的所有值。删除：DELETE FROM 表名 WHERE 条件 注意：不加where会删除表的所有记录。另外，delete后，再添加，id会不连续。还有一个删除表记录的是：TRUNCAT 表名 是DDL的DCL数据控制语言
修改密码：法1：mysqladmin -uroot -p旧密码 password 新密码法2：update mysql.user set password=PASSWORD('新密码') where user='root';若忘记了密码，则关闭数据库mysqld --skip-grant-tables然后mysql进入修改密码创建用户
CREATE USER 用户名 @'IP地址' IDENTIFIED BY '密码'此用户只能在指定的ip上使用，所有的IP要使用%用户授权
GRANT 权限1，权限2,... ON 数据库名.* TO 用户名 @IP地址例如：grant select，update，insert on shop.* to ‘test1’ @‘196.168.1.106’撤销权限
REVOKE 权限1，... ON 数据库名.* FROM 用户名 @ip地址用户创建与授权一句：
grant 权限1，... on 数据库名.* to 用户名@ip地址 identified by 密码flush privileges显示权限
show grant for 'test'@'192.16.8.1.0';删除用户DROP USER 用户名@IP地址=====================================================================
事务：一个sql语句就是一个事务，事务可以保证一组sql语句要么都成功要么都失败。
1. 可以关闭 set autocommit=0关闭自动提交，最后插入或修改时 只有commit才会最终存入。
2. 语句格式如下
start transaction; # start transaction 表示开启一个事务，或者用 begin 或 begin worksql语句。。。如果上面的sql语句没有问题commit（提交）-》写入磁盘如果有问题rollback（回滚）-》不写入磁盘
原子性：一个事务中的所有语句，要么全做，要么全不做。
一致性：让数据保持逻辑上的“合理性”，比如，一个商品出库时，既要仓库中的数目减1，又要让用户的商品数加1 。
隔离性：如果多个事务同时并发执行，但每一个事务就像各自执行一样。
持久性：一个事务执行成功，则对数据来说应该是一个明确的硬盘数据更改，而不仅仅是内存中的变化。
触发器
1、创建触发器
create trigger 触发器名 after / before insert / update / delete on 表名 for each row
begin sql语句 end;2、查看触发器
show create trigger 触发器名
3、删除触发器
drop trigger 触发器名
查询sql语句的执行时间的方法：
show variables like '%profiling%'
set profiling = on show profile for query 2建议：在where后的条件加索引。速度快。
使用 like 模糊时，右模糊（若有索引会用索引）快，左模糊和全模糊（不会用索引）慢。例如：右模糊 like 'wang%' 左模糊 like '%wang' 全模糊 like '%wang%'explain select * from user where pid = 123 or password = ‘555’ 查看具体语句的执行
- Mysql 三种连接
连接就是从两个表的笛卡尔积运算结果中选取某些行和列。换句话说，连接就是有条件的笛卡尔积。笛卡尔积关系运算：看这 的最后一项。
- INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
- LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
- RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
栗子：表stu1，表stu2
mysql> select * from stu1;+----------+---------+------+------+| id | name | sex | age |+----------+---------+------+------+| 12720510 | ljq | M | 26 || 12720511 | ljl | M | 25 || 12720512 | yangKai | M | 26 |+----------+---------+------+------+mysql> select * from stu2;+----------+-------------+-------+| id | course | score |+----------+-------------+-------+| 12720510 | Chemistry | 65 || 12720512 | Mathematics | 75 || 12720515 | C++ | 87 |+----------+-------------+-------+
select * from stu1,stu2; ---> 这就是笛卡尔积，like (a+b+c)*(d+e) = a*d + b*d + c*d + a*e + b*e + c*e
mysql> select * from stu1,stu2;+----------+---------+------+------+----------+-------------+-------+| id | name | sex | age | id | course | score |+----------+---------+------+------+----------+-------------+-------+| 12720510 | ljq | M | 26 | 12720510 | Chemistry | 65 || 12720511 | ljl | M | 25 | 12720510 | Chemistry | 65 || 12720512 | yangKai | M | 26 | 12720510 | Chemistry | 65 || 12720510 | ljq | M | 26 | 12720512 | Mathematics | 75 || 12720511 | ljl | M | 25 | 12720512 | Mathematics | 75 || 12720512 | yangKai | M | 26 | 12720512 | Mathematics | 75 || 12720510 | ljq | M | 26 | 12720515 | C++ | 87 || 12720511 | ljl | M | 25 | 12720515 | C++ | 87 || 12720512 | yangKai | M | 26 | 12720515 | C++ | 87 |+----------+---------+------+------+----------+-------------+-------+
加上where条件后，select * from stu1,stu2 where stu1.id = stu2.id;
mysql> select * from stu1,stu2 where stu1.id = stu2.id;+----------+---------+------+------+----------+-------------+-------+| id | name | sex | age | id | course | score |+----------+---------+------+------+----------+-------------+-------+| 12720510 | ljq | M | 26 | 12720510 | Chemistry | 65 || 12720512 | yangKai | M | 26 | 12720512 | Mathematics | 75 |+----------+---------+------+------+----------+-------------+-------+
select stu1.id, name, course, score from stu1.id = stu2.id; 选择部分列（相同列名的要加表名限制，像stu1.id）
mysql> select stu1.id, name, course, score from stu1,stu2 where stu1.id = stu2.id;+----------+---------+-------------+-------+| id | name | course | score |+----------+---------+-------------+-------+| 12720510 | ljq | Chemistry | 65 || 12720512 | yangKai | Mathematics | 75 |+----------+---------+-------------+-------+2 rows in set (0.00 sec)
- inner join
select * from stu1 inner join stu2;（可省略inner） 与 select * from stu1,stu2; 一样，都是笛卡尔积。
mysql> select * from stu1 inner join stu2; +----------+---------+------+------+----------+-------------+-------+| id | name | sex | age | id | course | score |+----------+---------+------+------+----------+-------------+-------+| 12720510 | ljq | M | 26 | 12720510 | Chemistry | 65 || 12720511 | ljl | M | 25 | 12720510 | Chemistry | 65 || 12720512 | yangKai | M | 26 | 12720510 | Chemistry | 65 || 12720510 | ljq | M | 26 | 12720512 | Mathematics | 75 || 12720511 | ljl | M | 25 | 12720512 | Mathematics | 75 || 12720512 | yangKai | M | 26 | 12720512 | Mathematics | 75 || 12720510 | ljq | M | 26 | 12720515 | C++ | 87 || 12720511 | ljl | M | 25 | 12720515 | C++ | 87 || 12720512 | yangKai | M | 26 | 12720515 | C++ | 87 |+----------+---------+------+------+----------+-------------+-------+9 rows in set (0.00 sec)
用 on 加条件，用 where 加条件。注：where后面增加的条件是对临时表生成后，进行过滤的。而on条件是在生成临时表时使用的条件。
mysql> select stu1.id, name, course, score, age from stu1 inner join stu2 on stu1.id = stu2.id;+----------+---------+-------------+-------+------+| id | name | course | score | age |+----------+---------+-------------+-------+------+| 12720510 | ljq | Chemistry | 65 | 26 || 12720512 | yangKai | Mathematics | 75 | 26 |+----------+---------+-------------+-------+------+2 rows in set (0.00 sec)mysql> select stu1.id, name, course, score, age from stu1 inner join stu2 where stu1.id = stu2.id;+----------+---------+-------------+-------+------+| id | name | course | score | age |+----------+---------+-------------+-------+------+| 12720510 | ljq | Chemistry | 65 | 26 || 12720512 | yangKai | Mathematics | 75 | 26 |+----------+---------+-------------+-------+------+2 rows in set (0.00 sec)
- left join
注意：left / right join 后不能不加on条件。可以这么写 select * from stu1 left join stu2 on 1=1；这样也是笛卡尔积。
mysql> select * from stu1 left join stu2;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> select * from stu1 left join stu2 on stu1.id = stu2.id;+----------+---------+------+------+----------+-------------+-------+| id | name | sex | age | id | course | score |+----------+---------+------+------+----------+-------------+-------+| 12720510 | ljq | M | 26 | 12720510 | Chemistry | 65 || 12720512 | yangKai | M | 26 | 12720512 | Mathematics | 75 || 12720511 | ljl | M | 25 | NULL | NULL | NULL |+----------+---------+------+------+----------+-------------+-------+3 rows in set (0.00 sec)
where 是在表连接之后在进行过滤的。on 是表连接时的条件。
mysql> select stu1.id, name, course, score from stu1 left join stu2 on stu1.id = stu2.id;+----------+---------+-------------+-------+| id | name | course | score |+----------+---------+-------------+-------+| 12720510 | ljq | Chemistry | 65 || 12720512 | yangKai | Mathematics | 75 || 12720511 | ljl | NULL | NULL |+----------+---------+-------------+-------+3 rows in set (0.00 sec)mysql> select stu1.id, name, course, score from stu1 left join stu2 on stu1.id = stu2.id and stu1.id=12720512;+----------+---------+-------------+-------+| id | name | course | score |+----------+---------+-------------+-------+| 12720512 | yangKai | Mathematics | 75 || 12720510 | ljq | NULL | NULL || 12720511 | ljl | NULL | NULL |+----------+---------+-------------+-------+3 rows in set (0.00 sec)mysql> select stu1.id, name, course, score from stu1 left join stu2 on stu1.id = stu2.id where stu1.id=12720512;+----------+---------+-------------+-------+| id | name | course | score |+----------+---------+-------------+-------+| 12720512 | yangKai | Mathematics | 75 |+----------+---------+-------------+-------+1 row in set (0.00 sec)mysql> select stu1.id, name, course, score from stu1 left join stu2 on stu1.id = stu2.id and stu2.id=12720512;+----------+---------+-------------+-------+| id | name | course | score |+----------+---------+-------------+-------+| 12720510 | ljq | NULL | NULL || 12720511 | ljl | NULL | NULL || 12720512 | yangKai | Mathematics | 75 |+----------+---------+-------------+-------+3 rows in set (0.00 sec)mysql> select stu1.id, name, course, score from stu1 left join stu2 on stu1.id = stu2.id where stu2.id=12720512;+----------+---------+-------------+-------+| id | name | course | score |+----------+---------+-------------+-------+| 12720512 | yangKai | Mathematics | 75 |+----------+---------+-------------+-------+1 row in set (0.00 sec)
- right join
mysql> select * from stu1 right join stu2 on stu1.id = stu2.id;+----------+---------+------+------+----------+-------------+-------+| id | name | sex | age | id | course | score |+----------+---------+------+------+----------+-------------+-------+| 12720510 | ljq | M | 26 | 12720510 | Chemistry | 65 || 12720512 | yangKai | M | 26 | 12720512 | Mathematics | 75 || NULL | NULL | NULL | NULL | 12720515 | C++ | 87 |+----------+---------+------+------+----------+-------------+-------+3 rows in set (0.00 sec)mysql> select stu1.id, name, course, score from stu1 right join stu2 on stu1.id = stu2.id;+----------+---------+-------------+-------+| id | name | course | score |+----------+---------+-------------+-------+| 12720510 | ljq | Chemistry | 65 || 12720512 | yangKai | Mathematics | 75 || NULL | NULL | C++ | 87 |+----------+---------+-------------+-------+3 rows in set (0.00 sec)mysql> select stu1.id, name, course, score from stu1 right join stu2 on stu1.id = stu2.id and stu1.id = 12720512;+----------+---------+-------------+-------+| id | name | course | score |+----------+---------+-------------+-------+| NULL | NULL | Chemistry | 65 || 12720512 | yangKai | Mathematics | 75 || NULL | NULL | C++ | 87 |+----------+---------+-------------+-------+3 rows in set (0.00 sec)mysql> select stu1.id, name, course, score from stu1 right join stu2 on stu1.id = stu2.id where stu1.id = 12720512;+----------+---------+-------------+-------+| id | name | course | score |+----------+---------+-------------+-------+| 12720512 | yangKai | Mathematics | 75 |+----------+---------+-------------+-------+1 row in set (0.00 sec)mysql> select stu1.id, name, course, score from stu1 right join stu2 on stu1.id = stu2.id and stu2.id = 12720512;+----------+---------+-------------+-------+| id | name | course | score |+----------+---------+-------------+-------+| 12720512 | yangKai | Mathematics | 75 || NULL | NULL | Chemistry | 65 || NULL | NULL | C++ | 87 |+----------+---------+-------------+-------+3 rows in set (0.00 sec)mysql> select stu1.id, name, course, score from stu1 right join stu2 on stu1.id = stu2.id where stu2.id = 12720512;+----------+---------+-------------+-------+| id | name | course | score |+----------+---------+-------------+-------+| 12720512 | yangKai | Mathematics | 75 |+----------+---------+-------------+-------+1 row in set (0.00 sec)
- group by
mysql> select * from stu2;+----------+-------------+-------+| id | course | score |+----------+-------------+-------+| 12720515 | C++ | 87 || 12720510 | Chemistry | 65 || 12720512 | Mathematics | 75 || 12720510 | C++ | 89 || 12720512 | Chemistry | 85 |+----------+-------------+-------+5 rows in set (0.00 sec)//group by id 以id为分组的依据//order by sum(score) asc; 以id为分组的分数之和排序（asc升序 desc降序，默认是升序）mysql> select id as xuehao, sum(score) as zongfen from stu2 group by id order by sum(score) asc;+----------+---------+| xuehao | zongfen |+----------+---------+| 12720515 | 87 || 12720510 | 154 || 12720512 | 160 |+----------+---------+3 rows in set (0.09 sec) mysql> select id as xuehao, avg(score) as average from stu2 group by id order by avg(score) desc;+----------+---------+| xuehao | average |+----------+---------+| 12720515 | 87.0000 || 12720512 | 80.0000 || 12720510 | 77.0000 |+----------+---------+3 rows in set (0.00 sec)mysql> select course as kemu, count(*) as renshu from stu2 group by course;+-------------+--------+| kemu | renshu |+-------------+--------+| C++ | 2 || Chemistry | 2 || Mathematics | 1 |+-------------+--------+3 rows in set (0.00 sec)
标准偏差：
1. 总体标准偏差 $$ \sigma = \sqrt{\frac{1}{N}\sum_{i=1}^{N}(x_i-\overline{x})^2} $$
2. 样本标准偏差 $$ \sigma = \sqrt{\frac{1}{N-1}\sum_{i=1}^{N}(x_i-\overline{x})^2} $$
mysql 求总体标准偏差：STD(expression) , STDDEV(expression), STDDEV_POP(expression)，总体标准差的平方：VAR_POP(expression)，VARIANCE(expression)
mysql 求样本标准偏差：STDDEV_SAMP(expression)，样本标准差的平方：VAR_SAMP(expression)
STDDEV(expression)计算当前行关于组的标准偏离
STDDEV_POP(expression)该函数计算总体标准偏离，并返回总体变量的平方根，其返回值与 VAR_POP 函数的平方根相同 STDDEV_SAMP(expression)该函数计算累积样本标准偏离，并返回样本变量的平方根，其返回值与 VAR_SAMP 函数的平方根相同mysql> select course as kemu, stddev(score) as STDEV, stddev_samp(score) as STDEVP from stu2 group by course;+-------------+-------+--------------------+| kemu | STDEV | STDEVP |+-------------+-------+--------------------+| C++ | 1 | 1.4142135623730951 || Chemistry | 10 | 14.142135623730951 || Mathematics | 0 | NULL |+-------------+-------+--------------------+3 rows in set (0.00 sec)//或者mysql> select course as kemu, std(score) as STDEV, stddev_samp(score) as STDEVP from stu2 group by course;+-------------+-------+--------------------+| kemu | STDEV | STDEVP |+-------------+-------+--------------------+| C++ | 1 | 1.4142135623730951 || Chemistry | 10 | 14.142135623730951 || Mathematics | 0 | NULL |+-------------+-------+--------------------+3 rows in set (0.00 sec)
mysql> select * from stu2;+----------+-------------+-------+| id | course | score |+----------+-------------+-------+| 12720515 | C++ | 87 || 12720510 | Chemistry | 65 || 12720512 | Mathematics | 75 || 12720510 | C++ | 89 || 12720512 | Chemistry | 85 |+----------+-------------+-------+5 rows in set (0.00 sec)mysql> select course as kemu, max(score) as MAX, min(score) as MIN from stu2 group by course;+-------------+------+------+| kemu | MAX | MIN |+-------------+------+------+| C++ | 89 | 87 || Chemistry | 85 | 65 || Mathematics | 75 | 75 |+-------------+------+------+3 rows in set (0.04 sec)