|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
启动，关闭，检查状态 mysql.server start mysql.server stop mysql.server status 登陆 mysql -uroot -p 修改密码(新密码123) ALTER USER 'root'@'localhost' IDENTIFIED by '123'; 常用命令 show databases; use test//切换到test库 select database();//查看选择的数据库 select version();//查看的数据库版本 select now();//查看时间 select user();//查看当前用户 创建、修改和删除数据库 create database (if not exists) test1 (character set utf8); alter database (if not exists) test1 (character set **); rename database test1 to test2; drop database (if exists) test1; |
数据类型
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
TINYINT -128 - 127 INT 2147***** BIGINT 2^63 FLOAT 4个字节 DOUBLE 8个字节 YEAR 1901-2155 yyyy TIME hh:mm:dd DATE yyyy-mm-dd DATETIME yyyy-mm-dd hh:mm:dd TIMESTAMP 字符型 CHAR 0-255 VARCHAR //变长 TINYTEXT TEXT LONGTEXT ENUM('男'，'女') |
表
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
查看 show tables (from database1); show colunms from table1; desc table1; show create table table1; 创建 create table table_name ( 列名 类型 约束; 列名 类型 约束; ); 例： create table student ( name varchar(10); age tinyint; ); 添加列,改列名，改列类型，改列位置，删除列 alter table table1 add colunm1 类型 (after colunm2//指定位置，不指定默认最后一列); alter table table1 change colunm1 colum2 类型; alter table table1 modify colunm1 类型2; alter table table1 modify colunm1 类型 after colunm2; alter table table1 drop colunm1; 删除表，修改表名 dorp table table1, table2; rename table info to info2; alter table table1 rename table2; 表分区 show plugins//查看是否只是表分区 例子 create table student ( name varchar(10); age tinyint; partition by range(age) ( partition p1 less than (40) ) ); 检索p1内的数据 select * from table2 partiton(p1); |
约束
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
1在创建表时后面直接指定约束 2在创建表时最后面直接指定约束 3通过alter增加或删除约束modify 主键和唯一可以用drop ky_id; 唯一约束用key/index 默认约束用set 1.主键约束 单字段主键 create table student ( name varchar(10) primary key; age tinyint; ); alter table table1 modify name varchar(10);//取消主键约束; alter table table1 modify name varchar(10) [constraint key1] primary key;//添加主键约束; alter table table1 add primary key (name); create table student ( name varchar(10) prime key; age tinyint; [constraint ky_id1] primary key (name); ); 多字段主键 create table student ( name varchar(10) prime key; age tinyint; [constraint ky_id1] primary key (name，age); ); 删除、添加主键 alter table table1 add primary key (name); alter table table1 drop primary key; 2.唯一约束 create table student ( name varchar(10) unique; age tinyint; ); alter table table1 modify name varchar(10);//取消唯一约束; alter table table1 modify name varchar(10) unique;//添加唯一约束; 删除、添加唯一约束 alter table table1 add [constraint ky_id1] unique (name); alter table table1 drop key/index 唯一约束名; 3.非空约束 create table student ( name varchar(10) not null; age tinyint; ); alter table table1 modify name varchar(10);//取消非空约束; alter table table1 modify name varchar(10) not null;//添加非空约束; 4.默认约束 create table student ( name varchar(10) default '张三'; age tinyint; ); alter table table1 modify colunm1 set default '张三';//添加 alter table table1 modify colunm1 //取消 alter table table1 modify colunm1 drop default '张三';//取消 ; 5.外键约束(on delete cascade) create table student ( name varchar(10) default '张三'; age tinyint; [constraint k_id1] foreign key (name) references table2 (colunm1); ); alter table table1 add [constraint k_id1] foreign key (name) references table2 (colunm1); alter table table1 drop foreign key 约束名; 自增 aotu_increment=2;//默认增长值为1;可在初始化时设置，modify时修改(增加，取消); 设置自增数值后可修改 alter table table1 auto_increment = 5; |
数据
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
1.插入完整数据 insert into 表名(列名) values(`````);//不指定列名默认插入完整数据 例insert into info(name,id) values ('张三'，201601); 例insert into info values ('张三'，201601); 插入部分数据 insert into 表名(列名) values(`````);// 例insert into info(name) values ('张三'); 插入多条记录 insert into 表名(列名) values(`````),(````),(`````);//括号里面数据，各个数据用','隔开; 输入查询数据 insert into 表名1 select * from 表名2 where id>5; 2.数据更新 update table1 set 列名=·· (where ``` )//没有where则默认所有行 例子 update info set name = '张三',id = id + 100 (where id>2016001); 3.删除 delete from table1 (where`````); 例子 delete from table1 (where id = 2016); 清空表 truncate table tabel |
查询select
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
1.查询 select 列名 from 表名 where; select * from table1(where id = 5); distinct//去重; select distinct id from table1; 查询并分组 select count(*) from table1 (where id > 5);//统计个数,where 限定条件 select id,count(*) from table1 group by id;//按照id分组 having 分组 select id from info group by id having count(id)>1; 查询后排序//不指定则默认升序 select * from info order by id asc, name desc;//id升序，id相同按照name降序 limit限制 select * from info limit 3;//前三行 select * from info limit 4,5;//第四行开始的五行 select name,count(*) from info group by name desc limit 1; 2.where { id > 5; id = 5; 运算符 = 等于 <>不等于 != 不等于 < > <= >= between and//between 5 and 6; is null;//检查空值，where id is null; and , or// 链接多个运算符，and 的运算级别比or高,课余括号连用 //例如where id > 5 and ( name < 9 or age > 8); in, not in //id in(5,8), id not in (5,8);匹配 } 3.like通配符{ % 匹配0，1，多个任意字符 _ 只能匹配1个字符 } 4.regexp正则表达式{ where name regexp '.三'; | 匹配几个字符串其中之一//例如'.pple|.dog|.cat'; 对于元字符，需要转义系列， 例如\\ \. \[ \] \+ \* \? [] 匹配多个字符的其中一个 例如 [abcd]pple [Aa]pple 匹 配区间 例如 [a-z]pple [0-9]pple [A-Z]pple [0-9a-zA-Z] [^0-9]//出0-9以外的字符 [[:alnum:]] [[:alpha:]] [[:digit:]] [[:lower:]] [[:uppper:]] . 匹配任何字符 + 匹配一个或多个字符 * 匹配0个或多个字符 ？ 匹配0或1个字符 {} 匹配重复次数 例如 a{0,4}, 最少出现0次，最多4次 a{3,}, 最少出现4次 a{3} 匹配3次 } |
创建计算字段
|
1 2 3 4 5 6 7 8 9 10 11 |
1.拼接字段 { select concat (name , '年龄为:', age ) from info;//拼接姓名与年龄 select concat (name , '年龄为:', age ) as new_colunm from info; //为这个拼接列起个名 ｝ 2.计算字段{ + - * / 加减乘除都可用 select age * age from info;//计算字段 select age * age as new_colunm from info ;//为这个字段列起个名字 ｝ 3.ltrim()//去掉头部空格。例如ltrim(id); 4.rtrim()//去掉末尾空格。例如rtrim(id); |
使用函数
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
字符串函数{ 1.upper 2.lower 3.length() 4.left() 5.right() 6.rtrim() 例如select upper(name) from info;//把名字换成大写展示 } 数学函数{ avg(); count(); max(); min(); sum(); 例如select sum(age*age) from info; select min(id), max(id), sum(id), count(id) from info, avg(age);//汇总 } |
分组
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
select id, count(id), avg(id) from info group id;//按照id分组，并显示每组的数量和平均值 where 按照行过滤，having按照组过滤 having{ select id , count(*) , max(id) from info group by id having count(*) > 2; } where + having{ select id , count(*) , max(id) from info where age > 90 group by id having count(*) > 2;//where把年龄90岁下的过滤了，having不能检测到90岁以下的了，count比不用where少 } 分组的同时可以排序{ select id , count(*) , max(id) from info group by id having count(*) > 2 order by id ; } |
子查询 和 表联结
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
想查询的数据列不在同一张表上{ info中有 id, name info2中有 id, age 查询张三的年龄{ select age from info2 where id = (select id from info where name = '张三'); select age from info2 where id in (select id from info where name = '张三'); 如果子查询的结果可能不唯一，可以用in//not in 也是可以适当用的 ｝ 想查张三的年龄，姓名和id select id, name, (select age from info2 where info.id = info2.id) where id = '张三'; //对于用重复的列名要用表名限定 select id, name, age form info, info2 where info.id = info2.id; select info.id, info.name, info2.age form info, info2 where info.id = info2.id; //从笛卡尔积中过滤 内连接{ select id, name, age form info inner join info2 on info.id = info2.id; //与where作用相同，从笛卡尔积中过滤 ｝ 子查询和联结表从多个表中的笛卡尔积中过滤，与单表过滤语法类似 自联结{ 想知道一个表中，某个学生的辅导员的其他学生的年龄 select age from info where fudaoyuan = (select fudaoyuan where name = '张三'); select p1.age from info as p1, info as p2 where p1.fudaoyuan = p2.fudaoyuan and p2.name = '张三';//吧一张表看成两个表，起个名字，从笛卡尔积中过滤； } } |
组合查询
|
1 2 3 4 5 6 |
union{union会过滤重复的行,不想过滤用union all 把两个查询语句放一起，查询结果放在一个表中 select * from info union select * from info2; } |
视图
|
1 2 3 4 5 6 7 8 9 10 11 12 |
创建视图{ creat view 视图名字 as select ................ 例如 create view test as select id, age2 from info, info2; 本次操作结果是把数据添加到一个视图里(这是一张特殊的表) 查看具体情况 desc test 查看创建语句 show creat table test 查看创建语句 show create view test show tables 则会显示所有的表和视图 更改视图名字 rename table test to test1; 总之视图是一张表，特殊的表，视图继承自表； } |
用户与权限管理
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
创建和删除用户{ create user xmj; drop user xmj; 重命名 rename user xmj to jmx; 密码设置 set password for xmj = password('xmj123'); } 权限的赋予与删除{ grant all on student.* to xmj;//赋予xmj在student库中所有表的所有权限(*.*代表所有库的所有表) revoke select on info from xmj;// alter insert update//等权限也可以 } 权限的查看{ show grants for xmj; } 数据库角色{ create role r1;//创建角色 grant select, insert on student.info to r1;//给角色赋予权限 grant r1 to xmj;//给用户赋予角色的权限 } |
数据库维护
|
1 2 3 |
analyze table info,info2;//检查表键是否正确 check table info, info2;//针对多种问题进行检查 help//查看帮助 |