mysql -uroot -proot --prompt 提示符 //登陆时修改mysql提示符
prompt 提示符 //登陆后 修改提示符
D 完整日期 //提示符可以使用的参数
d 当前数据库
h 服务器名称
u 当前用户
delimiter //设置结束符
创建数据库 //花括号为必选 中括号为可选
CREATE { DATABASE| SCHEMA } [ IF NOT EXISTS ] db_name [ DEFAULT ] CHARACTER SET [ = ] charset_name //忽略警告信息 //创建数据库使用的编码方式
SHOW WARNINGS //显示警告信息
CREATE DATABASE b1; //创建一个叫b1的数据库
查询当前服务器的所有数据库
SHOW { DATABASES | SCHEMAS} [ LIKE ' pattern ' | WHERE expr ]
SHOW DATABASES //查询当前服务器的数据库
SELECT DATABASE() //显示当前打开的数据库
SHOW CREATE DATABASE t1; //查询数据库的编码方式 如utf8
ALTER DATABASE b1 CHARACTER SET utf8; 将b1的编码方式改为utf8
删除数据库
DROP { DATABSE | SCHEMA } [ IF EXISTS ] db_name
DROP DATABSE b1; //删除b1数据库
USE b1 //打开b1数据库
创建数据表
CREATE TABLE [ IF NOT EXISTS ] table_name ( column_name data_type, ... ) // 创建数据表 列名称 数据类型
如：
mysql> CREATE TABLE tb1(
-> username VARCHAR(20),
-> age TINYINT UNSIGNED,
-> salary FLOAT(8,2) UNSIGNED
-> );
查看数据表
SHOW TABLES [ FROM db_name ] [ LIKE 'pattern' | WHERE expr ]
如：SHOW TABLES;
SHOW CREATE TABLE tb_name // 查看数据表存储引擎
查看数据表结构
SHOW COLUMNS FROM tb_name
如：SHOW COLUMNS FROM tb1;
插入记录
INSERT [ INTO ] tbl_name [( col_name,... )] VALUES(val,...) //如果col_name省略，就必须给全部字段都赋值，给自动编号的字段赋值，可以给null和default
如：1：INSERT tb1 VALUES('XU','20','9999.99'),(),(); //逗号分隔 插入多条记录
2：INSERT tb1 (username,salary) VALUES ('jM','123456.78');
方法2: INSERT [ INTO ] tbl_name SET col_name ={ expr | EDFAULT},... // 可以使用子查询
insert child2 set username='ben',age='18';
方法3：INSERT [ INTO ] tbl_name [(col_name,...)] SELECT ... //可以将结果插入到指定数据表
insert demo1(username) select username from demo where age >=20; //在demo中寻找age>=20的username插入demo1中，demo1中必须有username这一项
记录查找
SELECT expr,... FROM tbl_name
如： SELECT * FROM tb1; //查找所有记录
SELECT id as userid FROM tb1; // 查找tb1中的id属性，使用userid别名
NULL // 字段值可以为空值
NOT NULL // 字段值不能为空
如：
mysql> CREATE TABLE tb1(
-> username VARCHAR(20) NOT NULL, //字段不能为空
-> age TINYINT UNSIGNED NULL, //字段可以为空 可以为空 null可以不写
-> salary FLOAT(8,2) UNSIGNED
-> );
自动编号
AUTO_INCREMENT //自动编号，要和主键组合使用，默认情况下 起始值为1，每次增加1
主键约束 PRIMARY KEY
如：
mysql> CREATE TABLE tb2(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, // 自动编号 //主键
-> username VARCHAR(30) NOT NULL
-> );
唯一约束 UNIQUE KEY
DEFAULT // 默认约束
默认值，插入记录时，如果没有为字段赋值，则自动赋予默认值
mysql> CREATE TABLE tb1(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(30) NOT NULL UNIQUE KEY, //唯一约束
-> sex ENUM('1','2','3') DEFAULT'3' //默认值为3
-> );
FOREIGN KEYp
外键约束，保持数据一致性完整性，实现一对一或者一对多关系
编辑数据表的默认存储引擎
MySQL的配置文件 my.ini 中 default-storage-engine=INNODB;
{
父表
mysql> create table parent(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> pname VARCHAR(20) NOT NULL
-> );
子表
mysql> CREATE TABLE child( 1、要设置外键的字段不能为主键 2、该键所参考的字段必须为主键 3、两个字段必须具有相同的数据类型和约束
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> cname VARCHAR(20) NOT NULL,
-> pid SMALLINT UNSIGNED, //数据类型 和无负号字段需要相同
-> FOREIGN KEY (pid) REFERENCES parent(id) //参照parient（id）这个字段，把pid这个字段 设置为外键
-> );
}
SHOW INDEXES FROM parentG; 查看索引 G表示以网格形态展示
外键约束的参照操作
如：
{
父表
mysql> create table parent(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> pname VARCHAR(20) NOT NULL
-> );
子表
mysql> CREATE TABLE child(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> cname VARCHAR(20) NOT NULL,
-> pid SMALLINT UNSIGNED,
-> FOREIGN KEY (pid) REFERENCES parent(id) ON DELETE CASCADE //加上on delete和参数，父表的操作在子表映射
-> );
}
添加单列
ALTER TABLE tbl_name ADD [ COLUMN ] col_name column_definition [FIRST | AFTER col_name ]
ALTER TABLE child1 ADD age TINYINT UNSIGNED NOU NULL DEFAULT 10;
ALTER TABLE child1 ADD password VARCHAR(32) NOT NULL AFTER cname; //在cname后面插入新列
添加多列
ALTER TABLE tbl_name ADD [ COLUMN ] ( col_name column_definition, ... )
删除列
ALTER TABLE tbl_name DROP [ COLUMN ] col_name
ALTER TABLE child1 DROP age，DROP age1;
添加主键约束
ALTER TABLE child2 ADD CONSTRAINT PK_child2_id PRIMARY KEY (id); //自定义名字 可以不加
删除主键约束
ALTER TABLE child2 DROP PRIMARY key;
添加唯一约束
alter table child2 add unique key (username，...); //可添加多个
删除唯一约束
ALTER TABLE child2 DROP INDEX username; //先通过 show indexes from child2G; 找到index
添加外键约束
ALTER TABLE child2 ADD FOREIGN KEY(pid) REFERENCES parent(id);
删除外键约束
alter table child2 drop foreign key child2_ibfk_1; //先通过 show create table child2; 找到外键约束的名字
删除索引index
alter table child2 drop index pid;
添加默认约束
alter table tb_name alter age set default 15;
删除默认约束
ALTER TABLE child2 ALTER AGE DROP DEFAULT;
修改列定义
alter table child2 modify id smallint unsigned not null first; //原来存在的定义 可以修改 // 最后的first是将这个提升到第一个
修改列名称
alter table child2 change pid p_id TINYINT UNSIGNED NOT NULL FIRST; //可以修改列定义和列名称
修改数据表名称
alter table child2 rename child3;
RENAME TABLE child3 to child2;
表单更新记录
update demo set age = age +1;
UPDATE demo SET age = age +10 where id %2!=0; //where后面是条件 如果没有条件 所有记录都会更新
表单删除记录
delete from demo where id =1; //如果省略where 将全部被删除 删除后再添加 序号递增
select age from demo group by age; //对当前结果使用age进行分组
select age,username from demo group by 1 having age; //对分组条件进行指定
select * from demo order by age ，id desc; //对查询结果进行排序 desc为降序 默认为升序 比如age相同 就通过id降序排列
select * from demo limit 2; //指定返回值的数量 如果第一个参数不写 默认为0，limit 0 2； 从第0条开始，返回2条
AVG () 聚合函数 求平均值
select round(avg(goods_price),2)from tdb_goods; //对求值四舍五入 保留两位小数
子查询
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > (SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods)
ANY( 子查询 ) SOME(子查询) 符合任意一个都行 ALL( 子查询 ) 需要全部符合 // 子查询 相当于变量
[ NOT ] IN ( 子查询 )
[ NOT ] EXISTS ( 子查询 ) // 如果查询有返回值 为true 否则为false
多表更新
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;
//update后面跟要更新的表 // INNER JOIN 连接类型 内连接 后面跟要连接的表 //ON后面 两张表的连接条件 //SET 后面加更新值
update tdb_goods as a inner join tdb_goods_brands as b on a.brand_name =b.brand_name set a.brand_name = b.brand_id;
//两张表的内容名字相同 要通过as取一个别名
创建数据表的同时 将需要的数据导入
mysql> CREATE TABLE tdb_goods_brands(
-> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> brand_name VARCHAR(40) NOT NULL
-> )
-> SELECT brand_name FROM tdb_goods GROUP BY brand_name
-> ;
内连接 //仅显示符合连接条件的记录
select goods_id,goods_name,cate_name from tdb_goods inner join tdb_goods_cates on tdb_goods.cate_id = tdb_goods_cates.cate_idG;
左外连接 // 显示左表的全部 和符合连接条件的记录 右外连接同理
select goods_id,goods_name,cate_name from tdb_goods left join tdb_goods_cates on tdb_goods.cate_id = tdb_goods_cates.cate_idG;
多表连接
mysql> select goods_id,goods_name,cate_name,brand_name,goods_price from tdb_goods as g
-> inner join tdb_goods_cates as c on g.cate_id = c.cate_id
-> inner join tdb_goods_brands as b on g.brand_id = b.brand_idG;
无限极分类表 //自己连接自己
mysql> SELECT p.type_id,p.type_name,count(s.type_name) child_count FROM tdb_goods_types AS p
-> LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name order BY p.type_id;
多表删除 //单表模拟多表删除重复项
delete t1 from tdb_goods as t1 left join (select goods_id,goods_name from tdb_goods group by goods_name having count(goods_name)>=2) as t2 on t1.goods_name =t2.goods_name where t1.goods_id>t2.goods_id;
select concat('a','-','b') //a-b 字符连接
select concat(first,last) as result from test;
select concat_ws( ' | ' , 'a','b' ) //第一个位置写分隔符
select format(1250.75,0) //格式化数字，后面的0表示保留小数位数
select lower() //转为小写
select upper() //转为大写
select left('mysql',2) //my //获取左边起 两位字母
select right('mysql',2) //ql //获取右边起两位数
select length('aa aa') //5 //获取字符串长度 空格算一位
select ltrim(' demo') //去除前导空格
select rtrim('demo ') //去除后导空格
select trim(' demo ') //去除全部空格
select trim(leading '?' from '??demo???') //demo??? //删除字符串中 前导的问号
//leading 前导的 //trailing 后导的 //both 全删
select substring('mysql',1,2) //my 从第一位起 取两位数 从1开始计数 取值位置可以为负 从末尾数起
select substring('mysql',2) //yqsl 只带一个参数 就从那个数开始 取到结尾
select replace('?de?mo?', '?' ,'') // demo //将字符串的问号替换为空
select [ not ] like ()
select 'musql' like 'm%' // 1
select * from test where first_name like '%1%%' escape '1'; //相当于寻找符合要求的 //escape 把1作为转义符
%：任意个字符 _：任意一个字符
select ceil() //向上取整
select floor() //向下取整
select 3 mod 4 //取模 相当于%
select power(5,2) //5的2次方
select pound(3.652,2) //四舍五入 保留到小数点后2位
select truncate(5.568,2); //5.56 数字截取 可以为负
[ NOT ] BETWEEN ... AND .. //在...和...之间 正确返回1 否则为0
[ NOT ] IN() //在（）内能不能找到
IS [ NOT ] NULL //是否为空
select NOW() //当前日期和时间
select CURDATE() //当前日期
SELECT CURTIME() //当前时间
SELECT DATE_ADD('2018-10-10',INTERVAL 365 DAY) //在日期中 加365天 可以为负 单位可以为year，week
SELECT DATEDIFF('2018-10-10','2019-10-10') //计算差值
select date_format('2014-10-10','%m/%d/%y') //将格式改为 月/日/年
CONNECTION_ID() //连接id
DATEBASE() //当前数据库
LAST_INSERT_ID() //最后插入记录的ID号
USER() //当前用户
VERSION() //版本信息
AVG() //平均值
GOUNT() //计数
MAN() //最大值
MIN() //最小值
SUN() //求和
MD5() //信息摘要算法
PASSWORD() //密码算法
创建自定义函数
mysql> create function f1() returns varchar(30)
-> return date_format(now(),'%Y年%m月%d日 %h点%i分%s秒');
创建带参数的自定义函数
mysql> create function f2(num1 smallint unsigned,num2 smallint unsigned)
-> returns float(10,2) unsigned
-> return (num1+num2)/2;
创建不带参数的存储过程
create procedure sp1() select version() //存储过程 //过程体
call sp1 [()] 调用存储过程
创建带参数的存储过程和调用
mysql> delimiter /
mysql> create procedure idremove(in p_id int unsigned)
-> begin
-> delete from demo where id=p_id;
-> end
-> /
mysql> delimiter ;
mysql> call idremove(5);
DROP PROCEDURE sp_name //删除存储过程
mysql> create procedure removreturn(in p_id int unsigned,out usernum int unsigned)
-> begin
-> delete from demo where id=p_id;
-> select count(id) from demo into usernum; //将剩余的条数放入out的usernum里面 set @i = 7; 设置变量 用户变量
-> end
-> /
mysql> delimiter ;
mysql> call removereturn(10,@num); //将out输入的数据放入变量num中
select @num;
select row_count() //返回被插入或者删除或者更新的数量
存储引擎
MyISAM InnoDB Memory CSV Archive
default-storage-engine = '存储引擎'； //在mysql的my.ini 文件中修改
create table tb_name(
...
) engine = '存储引擎' //在创建数据表的时候定义
alter table tb_name engine [ = ] engine_name //通过命令修改
整数数据类型 // UNSIGNED 无符号位
浮点型
日期时间型
字符型
