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