[3/100]SQL语句全整理

SQL语句

一般操作

1.显示数据库

show databases;

2.显示当前mysql版本

show version();

3.连接数据库

mysql -u root -p******

数据库的操作

1.创建数据库

create database xxx;
create database xxx charset=utf8;

2.删除数据库

drop database xxx;

3.使用数据库

select database();
use xxx;

4.数据库权限

待补充！！

数据表的使用

1.显示数据库中所有的表

show tables;

2.创建数据库表

create table x1table(id int, name varchar(30));
create table x2table(id int primary key not null auto_increment, name varchar(30));

create table x3table(
    id int unsigned not null auto_increment primary key,
    name varchar(30),
    age tinyint unsigned default 0,
    high decimal(5,2),
    gender enum("男","女","中性","保密") default "保密",
    cls_id int unsigned
);

3.显示数据库表结构

desc x1table;

4.删除数据表

drop table x1table;

修改表结构

1.增加字段

alter table x3table add birthday datetime;

2.修改字段

alter table x3table modify birthday date;

3.修改字段名

alter table x3table change birthday birth date default "2000-01-01";

4.删除字段名

alter table x3table drop high;

5.设置外键

ALTER TABLE goods ADD FOREIGN KEY(cate_id) REFERENCES goods_cates(id);  --设置外键
ALTER TABLE goods DROP FOREIGN KEY goods_ibfk_1;  --goods_ibfk_1为外键名称，在SHOW CREATE TABLE goods中可以看到外键名称

数据类型

1.数字类型

2.字符串类型

3.时间类型

4.bit类型

数据的增删改查(CURD)

增，插入数据

--全部插入
insert into x3table values(0, "老王", 18, 188.88, "男", 0);
--部分插入
insert into x3table (name,gender) values ("金莲", "女");
--批量插入
insert into x3table (name,gender) values ("金莲", "女"), ("大乔","女");

修改数据

update x3table set age=23, gender=1 where id =3;

查询

--查询所有列
select * from x3table;
--查询条件(and 和 or 和 not)
select * from x3table where name="老王";
select * from x3table where id>2;
select * from x3table where id>2 and id<10;
--查询条件之 in
select * from x3table where id in (12, 15, 19);
select * from x3table where id not  in (12, 15, 19);
--查询条件之 between ... and ...
select * from x3table where id between 12 and 22;
select * from x3table where id not between 12 and 22;
--查询条件之判断空
select * from x3table where birth is null;
select * from x3table where birth is not null;
--查询指定列
select name,gender from x3table;
select name as 姓名,gender as 性别 from x3table;
select x.name x.gender from x3table as x;
--消除重复行
select distinct gender from x3table;
--模糊查询（like %替换1个或多个 _替换1个）
select * from x3table where name like "%老%";
--模糊查询之rlike，正则表达式
select * from x3table where name rikie "^小.*";
--排序
select * from x3table order by age asc;
select * from x3table order by age desc;
select * from x3table order by age desc, id desc;
--聚合函数（count sum max min avg 和 round）
select count(id) from x3table; --返回总行数
select max(age) from x3table; --返回最大值
select avg(age) from x3table; --返回平均值
select round(sum(age)/count(id), 2) from x3table; --保留小数点位数
--分组 group by
select avg(age), gender from x3table group by gender;  --要跟聚合一起用
select gender, group_concat(name,"_",id,"_",age) from x3table group by gender;  --group_concat
select gender, group_concat(name,"_",id,"_",age), avg(age) from x3table group by gender having avg(age)>22;  --having对查出来点结果进行条件判断
--分页
select * from x3table limit 3;  --limit 个数
select * from x3table limit 0, 5;  --limit(第N页-1)*每页的个数，每页的个数
select * from x3table limit 1, 5;  --limit(第N页-1)*每页的个数，每页的个数
--连接查询
--内连接
select * from students inner join classes on students.cls_id = classes.id;
--左连接
select * from students as s left join classes as c on s.clsid = c.id;  --右连接把两个表点顺序换下就行
--子查询
select * from student where height = (select max(height) from students);

删除

delete from x3table where id=3;

其他

数据库引擎

主要有两种：MyISAM和InnoDB，主要区别是InnoDB支持事务处理与外键和行级锁。

show create table x3table;

数据库高级用法

视图

用来更方便地查询数据

CREATE VIEW v_goods_info as (select语句)

视图的作用：
1、提高了重用性；
2、对数据库重构时，不影响程序的运行；
3、提高了安全性能，可以对不同用户；
4、让数据更加清晰。

事务

start transaction;
select balance from checking where customer_id = 10233276;
update checking set balance = balance - 200.00 where customer_id = 10233276;
update savings set balance = balance + 200.00 where cunstomer_id = 10233276;
commint;(或者rollback)

事务的四大特性：原子性、一致性、隔离性、持久性。

索引

--创建索引
CREATE INDEX title_index ON test_table(title(10))
--查看索引
SHOW INDEX FROM test_table
--删除索引
DROP INDEX title_index ON test_table

账号管理

所有的用户及权限信息存储在mysql数据库中的user表。

SHOW databases;
USE mysql;
SHOW tables;
SELECT user, host, authentication_string FROM user;

创建账户&授权

GRANT 权限列表 ON 数据库[.表] TO '用户名'@'访问主机' IDENTIFIED BY '密码';
GRANT select ON test_table.* TO 'ttt'@'localhost' IDENTIFIED BY '123456';

修改权限

GRANT SELECT, INSERT ON test_table.* TO 'ttt'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;  --刷新

修改用户密码

UPDATE user SET authentication_string('123') where user='ttt';
FLUSH PRIVILEGES;  --刷新

远程登录

mysql -u root -p*** -h11.11.1.1 -p3306

删除用户

DROP user '用户名'@'主机';
--或者
DELETE FROM user WHERE user='用户名';

主从

目的：
读写分离
数据备份
负载均衡

--备份数据库到tt.sql
mysqldump -uroot -p****** test_table > tt.sql
--恢复数据库，先要创建新的数据库
mysql -uroot -p****** 新数据库名 < tt.sql