Mysql相关笔记1
create table [if not exists] tbl_name{
字段名称 字段类型【完整性约束条件】
}engine = 引擎名称 charset =’编码方式’;
注释内容:
# 注释内容
-- 注释内容
用反引号是防止我们的名称和mysql关键字冲突。
-create database if not exists 'maizi';
use 'maizi';
-- 编号 id
-- 用户名 username
-- 年龄 age
-- 性别 sex
-- 邮箱 email
-- 地址 addr
-- 生日 birth
-- 薪水 salary
-- 电话 tel
-- 是否结婚
-- 字段注释 通过comment
create table if not exists user(
id smallint,
username varchar(20),
age tinyint,
sex enum('男','女','保密'),
email varchar(30),
addr varchar(50),
birth year,
salary float(8,2),
tel int,
mrraied tinyint(1) comment '0代表结婚，非0代表未结婚'
)engine=innodb charset=utf8;
mysql> select *from test4;
+-------+-------+-------+
| num1 | num2 | num3 |
+-------+-------+-------+
| 1.10 | 2.20 | 3.30 |
| 11.10 | 2.20 | 3.30 |
| 11.11 | 2.20 | 3.30 |
| 11.11 | 55.55 | 66.66 |
+-------+-------+-------+
4 rows in set (0.00 sec)
mysql> insert test4 values(11.11111,55.55555,66.666666);
query ok, 1 row affected, 1 warning (0.00 sec)
mysql> select *from test4;
+-------+-------+-------+
| num1 | num2 | num3 |
+-------+-------+-------+
| 1.10 | 2.20 | 3.30 |
| 11.10 | 2.20 | 3.30 |
| 11.11 | 2.20 | 3.30 |
| 11.11 | 55.55 | 66.66 |
| 11.11 | 55.56 | 66.67 |
+-------+-------+-------+
5 rows in set (0.00 sec)
mysql> show warnings;
empty set (0.00 sec)
mysql> select * from test4 where num1='11.11';
empty set (0.00 sec)
mysql> select * from test4 where num1=11.11;
+-------+-------+-------+
| num1 | num2 | num3 |
+-------+-------+-------+
| 11.11 | 2.20 | 3.30 |
| 11.11 | 55.55 | 66.66 |
| 11.11 | 55.56 | 66.67 |
+-------+-------+-------+
3 rows in set (0.00 sec)
mysql> select * from test4 where num3='3.30';
+-------+------+------+
| num1 | num2 | num3 |
+-------+------+------+
| 1.10 | 2.20 | 3.30 |
| 11.10 | 2.20 | 3.30 |
| 11.11 | 2.20 | 3.30 |
+-------+------+------+
3 rows in set (0.00 sec)
以上3个黄色的部分我们可以得到一些结论:
float 和 double是纯数字的，而decimal是以字符串的形式来存储，当你查询数据的时候就，如果你查的是字符串形式的数字，那么你只能根据decimal来查询，而当你以数字的形式查询的时候，都可以查询到。
char和varchar的区别:
char是定长的，varchar是变长的，char不保留结尾空格空格，varchar保留结尾空格,char和varchar都保留开头空格。
mysql> select length('啊');----------------字节长度utf8中1个字符占3个字节
+---------------+
| length('啊') |
+---------------+
| 3 |
+---------------+
1 row in set (0.00 sec)
mysql> select char_length('啊');-----------字符长度
+--------------------+
| char_length('啊') |
+--------------------+
| 1 |
+--------------------+
1 row in set (0.00 sec)
tinytext
text
mediumtext
longtext
数据检索效率是 char(速度快，占用空间大，用空间换速度)>varchar(空间小，速度稍微慢)>text(速度最慢，一般能不用就不用)
枚举类型：enum索引从1开始 ，可以insert tablename values(索引)，添加枚举。可以insert null，不可以insert空字符串。
日期时间类型:
time
date
datetime
timestamp
year---------- 可以直接insert数字，也可以插入字符串
time----------- 可以字符串 例如’1 12:12:12’ 也可以直接插入数字’123456’ 显示12:34:56直接插入数字12是00:00:12
完整性约束条件:
primary key 主键 ---主键类似唯一标识符
auto_incretement 自增长 -------默认从1开始增长，每次加1,与主键配合使用，被标注自增长的字段一定是主键,但是主键不一定是自增长的。
foreign key 外键
not null 非空
unique key 唯一 ----------------------key可以省略，而primary_key的primary可以省略掉,null不算重复。s
default 默认值
create table ifnot exists user1(
id int primary key,
username varchar(20)
);
-- 有了主键 可以用主键来进行查询
select * from user1where id = 1;
create table ifnot exists user2(
id int,
username varchar(20),
card varchar(15),
primary key(id,card) ---------主键合并(id和 card任意一个项与已有的不同都被看作不是一个主键)
);
mysql> show create table user2; -----显示创建表细节
| user2 | create table `user2` (
`id` int(11) not null,
`username` varchar(20) default null,
`card` varchar(15) not null,
primary key (`id`,`card`)
) engine=innodb default charset=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------
create table user3(
id int key auto_increment,
username varchar(20)
);
-- 指定auto—_increment 初始值
create table user4(
id int key auto_increment,
username varchar(20)
)auto_increment 100;
mysql> show create table user4;
| user4 | create table `user4` (
`id` int(11) not null auto_increment,
`username` varchar(20) default null,
primary key (`id`)
) engine=innodb auto_increment=100 default charset=utf8 |
+-------+---------------------------------------------------------------------
alter table user4 auto_increment= 500;
改变自增长值
--create table [if not exists] tbl_name(
--字段名称 字段类型 [unsigned|zerofill] [not null] [default 默认值] [[primary] key | unique [key]] [auto_increment]
--)engine = charset = auto_increment =
create table user7 (
id int unsigned not null default 20key unique
);
2.7日:
表的重命名:
a.)alter talbe tbl_name rename [to|as] newname;to和as都可以省略掉
b.)rename table tbl_name to newname 注意这个to是不可以省略掉的
alter table user7 rename to user8;
alter table user8 rename user7;
添加字段和删除字段:
alter table tbl_name add 字段名称 [完整性约束条件] [first | after字段名称 ]
一次添加好几个字段:
alter table user8
add num1 varchar(20) not null default 'a' first,
add num2 enum('1','2','3'),
add num3 int unsigneddefault 30 after card,
alter table user8add num4 varchar(20) not null first;
删除字段:
alter table tbl_name drop 字段名称
-- 一次删除多个字段
alter table user8
drop num1,
drop num2,
drop num3;
修改字段:
modify不改名字 ---主要用于修改字段的数据类型
change 改变名字 --- 主要用于修改字段的名字和数据类型 change更灵活
-- 修改字段
alter table user8modify num1 int not null default 10;
alter table tbl_name modify 字段名称 [完整性约束条件] [first|after字段名称]
alter table tbl_name change旧字段名称新字段名称 [完整性约束条件] [first|after字段名称]
添加默认值:
--添加默认值和删除默认值
alter table tbl_namealter 字段名称set default 默认值
alter table tbl_namealter 字段名称drop default
添加和删除主键:
alter table tbl_name add primary key(字段名称)
alter table tbl_name drop rpimary key
需要注意的是：当一个字段 即是主键又是自增长的时候，你无法直接删除它的主键，因为只有主键是自增长的，所以你应该先修改字段，把他变成非自增长的，然后你就可以删除掉主键了。
添加唯一索引(可以用来添加唯一索引和复合索引):
-- 唯一索引
alter table tbl_name [constraint [symbol]]unique [index|key] [索引名称](字段名称,....)
alter table tbl_namedrop {index |key}index_name-- 索引名称
注意 当你要删除索引的时候， 你的 index 和key就不可以省略了
例子:
create table user10(
num1 int,
num2 int,
num3 int
);
alter table user10add unique index n1 (num1);
alter table user10add unique index mulindex(num2,num3);
注意 上述的n1和 mulindex是我们指定的索引名称。
我们调用
show create table user10;显示我们创建表的细节如下：
| user10 | create table `user10` (
`num1` int(11) default null,
`num2` int(11) default null,
`num3` int(11) default null,
unique key `n1` (`num1`),
unique key `mulindex` (`num2`,`num3`)
) engine=innodb default charset=utf8 |
可以看到我们的索引名称，n1和nulindex使用索引的时候就用他们
alter table user10drop index n1;调用这个命令成功删除n1唯一索引。
删除数据表:
drop table [if exists] tbl_name,....
-- 删除数据表
drop table ifexists user99,user1,user2,user3,user4,user5,user6,user7,user9,user10;
插入记录:
create table ifnot exists user1(
id int unsigned key auto_increment,
username varchar(20)unique not null,
password varchar(20)not null,
age int(10) not null default 18
);
-- 第一种:不指定具体的字段名
insert [into] tbl_name values|values(值,.....)
insert user1 values (1,'卡罗尔','卡罗尔',default);
insert user1 value (2,'黑人','黑人',35);
-- 第二种:列出指定字段
insert [into] tbl_name(字段名称,...) values|value(对应字段的值,.....)
insert into user1(username,password)values('尼根','boss');
+----+-----------+-----------+-----+
| id | username | password | age |
+----+-----------+-----------+-----+
| 1 | 卡罗尔 | 卡罗尔 | 18 |
| 2 | 黑人 | 黑人 | 35 |
| 3 | 尼根 | boss | 18 |
+----+-----------+-----------+-----+
-- 第三种:同时插入多条记录(需要注意，只能用values，此时value失效)
insert [into] tbl_name(字段名称,...)values(值,...),(值,...),(值,...)
insert user1(username,password)values('卢西尔','weapon'),('瑞克','leader');
-- 第四种:通过set形式插入记录
insert [into] tbl_nameset 字段名称=值,...
insert user1 set username='刀女',password='强力输出',age=20;
+----+-----------+--------------+-----+
| id | username | password | age |
+----+-----------+--------------+-----+
| 1 | 卡罗尔 | 卡罗尔 | 18 |
| 2 | 黑人 | 黑人 | 35 |
| 3 | 尼根 | boss | 18 |
| 4 | 卢西尔 | weapon | 18 |
| 5 | 瑞克 | leader | 18 |
| 6 | 刀女 | 强力输出 | 18 |
+----+-----------+--------------+-----+
-- 第五种:将查询结果插入到表中
insert [into] tbl_name[(字段名称,...)]select 字段名称from tbl_name [where条件]
-- 新建个表，将user1 插入到新建的表中
create table ifnot exists test(
id int key auto_increment,
username varchar(20),
password varchar(20)
);
-- 将user1里id大于2的内容插入到test里
insert test(username,password)select username,passwordfrom user1 where id>2;
mysql> select * from test;
+----+-----------+--------------+
| id | username | password |
+----+-----------+--------------+
| 1 | 尼根 | boss |
| 2 | 卢西尔 | weapon |
| 3 | 瑞克 | leader |
| 4 | 刀女 | 强力输出 |
+----+-----------+--------------+
-- 如果待插入的表test1的字段不匹配，但是不匹配的字段有默认值，这样可以插入成功吗?
create table ifnot exists test1(
id int key auto_increment,
username varchar(20),
password varchar(20),
age int(10) unsigneddefault 18,
sex tinyint(0) default 0
);
insert test1 select username,password from user1;
显示:
error 1136 (21s01): column count doesn't match value count at row 1
但是要注意:
insert test1(username,password)select username,passwordfrom user1;
如果此时你指明了待插入的字段，那么即使字段不匹配，也行的通。
显示:
mysql> insert test1(username,password) select username,password from user1;
query ok, 6 rows affected (0.00 sec)
mysql> select * from test1;
+----+-----------+--------------+------+------+
| id | username | password | age | sex |
+----+-----------+--------------+------+------+
| 1 | 卡罗尔 | 卡罗尔 | 18 | 0 |
| 2 | 黑人 | 黑人 | 18 | 0 |
| 3 | 尼根 | boss | 18 | 0 |
| 4 | 卢西尔 | weapon | 18 | 0 |
| 5 | 瑞克 | leader | 18 | 0 |
| 6 | 刀女 | 强力输出 | 18 | 0 |
+----+-----------+--------------+------+------+
更新数据:
-- 更新数据
update tbl_name set 字段名称 = 值,....[where条件] [order by 字段名称] [limit 限制条数]
update user1 set age= 16;-- 不加条件会改变所有记录
+----+-----------+--------------+-----+
| id | username | password | age |
+----+-----------+--------------+-----+
| 1 | 卡罗尔 | 卡罗尔 | 16 |
| 2 | 黑人 | 黑人 | 16 |
| 3 | 尼根 | boss | 16 |
| 4 | 卢西尔 | weapon | 16 |
| 5 | 瑞克 | leader | 16 |
| 6 | 刀女 | 强力输出 | 16 |
update user1 set username = '以西结',password = 'king',age = 35 where id=3;-- 一下更改记录的多个字段，使用where条件
+----+-----------+--------------+-----+
| id | username | password | age |
+----+-----------+--------------+-----+
| 1 | 卡罗尔 | 卡罗尔 | 16 |
| 2 | 黑人 | 黑人 | 16 |
| 3 | 以西结 | king | 35 |
| 4 | 卢西尔 | weapon | 16 |
| 5 | 瑞克 | leader | 16 |
| 6 | 刀女 | 强力输出 | 16 |
+----+-----------+--------------+-----+
删除数据:
-- 删除数据
delete from tbl_name [where条件] [order by 字段名称] [limit 限制条数]----不重置auto_increment,你需要alter table tbl_name auto_increment = xx
truncate [table] tbl_name-- 彻底清空表，并且重置auto_increment
查询数据操作(dql):\
a.)查询表达式: 每一个表达式表示想要的一列，必须至少有一列，多个列之间用,隔开
*表示所有列,tbl_name.*可以表示命名表的所有列(这个tbl_name.*在多表查 询的时候很有用)。
查询表达式可以使用[as]alias_name为其赋予别名
-- 查询数据
select * from user1;
select user1.* from user1; -- 多表查询很有用
-- 查询想要的列，多个字段之间用,分隔
select id,username from user1;
-- 指定表来自那个下的 用db_name.tbl_name的形式
select id,password from test.user1;
-- 字段来自于哪张表
select user1.id,user1.password from test.user1;
-- 发现每次要输入好多表名，太长了 可以给表名和字段名都起别名
-- 给表名起别名 ------ 方便基于表的操作
select a.id,a.username,a.password,a.agefrom test.user1 as a;
-- 给字段起别名------ 查询结果显示的是字段的别名
select a.id as '编号',a.usernameas '账号',a.passwordas '密码',a.ageas '年龄' from user1as a;
+--------+-----------+--------------+--------+
| 编号 | 账号 | 密码 | 年龄 |
+--------+-----------+--------------+--------+
| 1 | 卡罗尔 | 卡罗尔 | 16 |
| 2 | 黑人 | 黑人 | 16 |
| 3 | 以西结 | king | 35 |
| 4 | 卢西尔 | weapon | 16 |
| 5 | 瑞克 | leader | 16 |
| 6 | 刀女 | 强力输出 | 16 |
+--------+-----------+--------------+--------+
b.) 按条件查询-- where条件
!=、!>、!<、<=>(也是等号，只是可以等于null,普通的=不可以等于null)
是否为空值
is null、is not null
-- 比较符查询
alter table user1 modify ageint default 18;
insert user1 values(7,'摩根','反省',null);
select * from user1where age=null;
+----+-----------+--------------+------+
| id | username | password | age |
+----+-----------+--------------+------+
| 1 | 卡罗尔 | 卡罗尔 | 16 |
| 2 | 黑人 | 黑人 | 16 |
| 3 | 以西结 | king | 35 |
| 4 | 卢西尔 | weapon | 16 |
| 5 | 瑞克 | leader | 16 |
| 6 | 刀女 | 强力输出 | 16 |
| 7 | 摩根 | 反省 | null |
+----+-----------+--------------+------+
7 rows in set (0.00 sec)
mysql> select * from user1 whereselect * from user1 where age=null;
error 1064 (42000): you have an error in your sql syntax;check the manual that corresponds to your mysql server versionfor the right syntax to use near'* from user1 where age=null' at line 1
mysql> select * from user1 where age=null;
empty set (0.00 sec)---- 我们明明有有空值 =不用和null做比较 此时我们应该用<=>
-- 用<=>与null作比较
select * from user1where age<=> null;
+----+----------+----------+------+
| id | username | password | age |
+----+----------+----------+------+
| 7 | 摩根 | 反省 | null |
+----+----------+----------+------+
-- is null、is not null
select * from user1where age is null;
+----+----------+----------+------+
| id | username | password | age |
+----+----------+----------+------+
| 7 | 摩根 | 反省 | null |
+----+----------+----------+------+
select * from user1where age is not null;
+----+-----------+--------------+------+
| id | username | password | age |
+----+-----------+--------------+------+
| 1 | 卡罗尔 | 卡罗尔 | 16 |
| 2 | 黑人 | 黑人 | 16 |
| 3 | 以西结 | king | 35 |
| 4 | 卢西尔 | weapon | 16 |
| 5 | 瑞克 | leader | 16 |
| 6 | 刀女 | 强力输出 | 16 |
+----+-----------+--------------+------+
c.)范围查询
-- 范围查询
between x1 and x2 、not between x1and x2 ;---在x1和x2之间和不在x1和x2之间的记录
-- 比如
select * from user1 where idbetween 3 and 5;
+----+-----------+----------+------+
| id | username | password | age |
+----+-----------+----------+------+
| 3 | 以西结 | king | 35 |
| 4 | 卢西尔 | weapon | 16 |
| 5 | 瑞克 | leader | 16 |
+----+-----------+----------+------+
select * from user1 where id not between 3and 5;
+----+-----------+--------------+------+
| id | username | password | age |
+----+-----------+--------------+------+
| 1 | 卡罗尔 | 卡罗尔 | 16 |
| 2 | 黑人 | 黑人 | 16 |
| 6 | 刀女 | 强力输出 | 16 |
| 7 | 摩根 | 反省 | null |
+----+-----------+--------------+------+
-- 此时我们发现一个问题,between是一个比较死板的范围 ，如果我们要id = 1,3,5,7,9等等的id怎么办?如果我们要更加分散的记录怎么办?此时in关键字发挥作用
select * from user1where id in(1,3,5,7);
+----+-----------+-----------+------+
| id | username | password | age |
+----+-----------+-----------+------+
| 1 | 卡罗尔 | 卡罗尔 | 16 |
| 3 | 以西结 | king | 35 |
| 5 | 瑞克 | leader | 16 |
| 7 | 摩根 | 反省 | null |
+----+-----------+-----------+------+
select * from user1where id not in(1,3,5,7);
+----+-----------+--------------+------+
| id | username | password | age |
+----+-----------+--------------+------+
| 2 | 黑人 | 黑人 | 16 |
| 4 | 卢西尔 | weapon | 16 |
| 6 | 刀女 | 强力输出 | 16 |
+----+-----------+--------------+------+
d.)模糊查询
-- 模糊查询
-- %代表0个1个或者多个任意字符
-- _代表一个任意字符
select * from user1where username like '卡%';
+----+-----------+-----------+------+
| id | username | password | age |
+----+-----------+-----------+------+
| 1 | 卡罗尔 | 卡罗尔 | 16 |
+----+-----------+-----------+------+
-- 查询中间包含字
select * from user1where username like '%西%';
+----+-----------+----------+------+
| id | username | password | age |
+----+-----------+----------+------+
| 3 | 以西结 | king | 35 |
| 4 | 卢西尔 | weapon | 16 |
+----+-----------+----------+------+
-- 因为_代表一位任意字符，所以可以使用几个_来代表几位字符
select * from user1where username like '___';
+----+-----------+-----------+------+
| id | username | password | age |
+----+-----------+-----------+------+
| 1 | 卡罗尔 | 卡罗尔 | 16 |
| 3 | 以西结 | king | 35 |
| 4 | 卢西尔 | weapon | 16 |
+----+-----------+-----------+------+
-- _和%搭配使用
select * from user1where username like '__尔%';
+----+-----------+-----------+------+
| id | username | password | age |
+----+-----------+-----------+------+
| 1 | 卡罗尔 | 卡罗尔 | 16 |
| 4 | 卢西尔 | weapon | 16 |
+----+-----------+-----------+------+
e.)逻辑运算符
-- 逻辑运算符 and 和or 没什么好说的，下面写个复杂的查询语句
select id,username,password,agefrom user1where agebetween 16and 100and usernamelike '___' and idin(3,4,6,5,7,10,11,13)or passwordis not null;
+----+-----------+--------------+------+
| id | username | password | age |
+----+-----------+--------------+------+
| 1 | 卡罗尔 | 卡罗尔 | 16 |
| 2 | 黑人 | 黑人 | 16 |
| 3 | 以西结 | king | 35 |
| 4 | 卢西尔 | weapon | 16 |
| 5 | 瑞克 | leader | 16 |
| 6 | 刀女 | 强力输出 | 16 |
| 7 | 摩根 | 反省 | null |
+----+-----------+--------------+------+
f.)分组查询
-- 分组查询 group by(分完组只显示第一个记录 )
-- 先向user1里添加sex字段
alter table user1add sex enum('男','女','保密');
-- 然后更新user1里各记录的sex字段属性值
update user1 set sex='男' where idin (1,3,5,7);
update user1 set sex='女' where idin (4,6);
update user1 set sex='保密' where idin(2);
+----+-----------+--------------+------+--------+
| id | username | password | age | sex |
+----+-----------+--------------+------+--------+
| 1 | 卡罗尔 | 卡罗尔 | 16 | 男 |
| 2 | 黑人 | 黑人 | 16 | 保密 |
| 3 | 以西结 | king | 35 | 男 |
| 4 | 卢西尔 | weapon | 16 | 女 |
| 5 | 瑞克 | leader | 16 | 男 |
| 6 | 刀女 | 强力输出 | 16 | 女 |
| 7 | 摩根 | 反省 | null | 男 |
+----+-----------+--------------+------+--------+
-- 下面按sex分组
select * from user1group by sex;
+----+-----------+-----------+------+--------+
| id | username | password | age | sex |
+----+-----------+-----------+------+--------+
| 1 | 卡罗尔 | 卡罗尔 | 16 | 男 |
| 4 | 卢西尔 | weapon | 16 | 女 |
| 2 | 黑人 | 黑人 | 16 | 保密 |
+----+-----------+-----------+------+--------+
-- 配合group_concat()得到分组详情
-- count(字段)、max(字段)、min(字段)、avg(字段)、sum(字段)配合聚合函数 需要注意的是count(字段)不统计null值
-- 配合with rollup记录上面所有记录的总和
select id,sex,group_concat(username)from user1 group by sex;
| id | sex | group_concat(username) |
+----+--------+-----------------------------------+
| 1 | 男 | 卡罗尔,以西结,瑞克,摩根 |
| 4 | 女 | 卢西尔,刀女 |
| 2 | 保密 | 黑人 |
+----+--------+-----------------------------------+
-- 其他几个函数的调用
select id,sex,group_concat(username)as '用户名' ,max(age)as '最大年龄',avg(age)as '平均年龄',count(age),sum(age) as '年龄总和' from user1group by sex;
+----+--------+-----------------------------------+--------------+--------------+------------+--------------+
| id | sex | 用户名 | 最大年龄 | 平均年龄 | count(age) | 年龄总和 |
+----+--------+-----------------------------------+--------------+--------------+------------+--------------+
| 1 | 男 | 卡罗尔,以西结,瑞克,摩根 | 35 | 22.3333 | 3 | 67 |
| 4 | 女 | 卢西尔,刀女 | 16 | 16.0000 | 2 | 32 |
| 2 | 保密 | 黑人 | 16 | 16.0000 | 1 | 16 |
+----+--------+-----------------------------------+--------------+--------------+------------+--------------+
select group_concat(id),group_concat(password),group_concat(username)as '用户名' ,max(age)as '最大年龄',avg(age)as '平均年龄',count(age),sum(age) as '年龄总和' from user1group by sex;
+------------------+------------------------------+-----------------------------------+--------------+--------------+------------+--------------+
| group_concat(id) | group_concat(password) | 用户名 | 最大年龄 | 平均年龄 |count(age) | 年龄总和 |
+------------------+------------------------------+-----------------------------------+--------------+--------------+------------+--------------+
| 1,3,5,7 | 卡罗尔,king,leader,反省 | 卡罗尔,以西结,瑞克,摩根 | 35 | 22.3333 | 3 | 67 |
| 4,6 | weapon,强力输出 | 卢西尔,刀女 | 16 | 16.0000 | 2 | 32 |
| 2 | 黑人 | 黑人 | 16 | 16.0000 | 1 | 16 |
+------------------+------------------------------+-----------------------------------+--------------+--------------+------------+--------------+
-- 加上with rollup
select group_concat(id),group_concat(password),group_concat(username)as '用户名' ,max(age)as '最大年龄',avg(age)as '平均年龄',count(age),sum(age) as '年龄总和' from user1group by sexwith rollup;
*************************** 1. row ***************************
group_concat(id): 1,3,5,7
group_concat(password): 卡罗尔,king,leader,反省
用户名: 卡罗尔,以西结,瑞克,摩根
最大年龄: 35
平均年龄: 22.3333
count(age): 3
年龄总和: 67
*************************** 2. row ***************************
group_concat(id): 4,6
group_concat(password): weapon,强力输出
用户名: 卢西尔,刀女
最大年龄: 16
平均年龄: 16.0000
count(age): 2
年龄总和: 32
*************************** 3. row ***************************
group_concat(id): 2
group_concat(password): 黑人
用户名: 黑人
最大年龄: 16
平均年龄: 16.0000
count(age): 1
年龄总和: 16
*************************** 4. row ***************************
group_concat(id): 1,3,5,7,4,6,2
group_concat(password): 卡罗尔,king,leader,反省,weapon,强力输出,黑人
用户名: 卡罗尔,以西结,瑞克,摩根,卢西尔,刀女,黑人
最大年龄: 35
平均年龄: 19.1667
count(age): 6
年龄总和: 115
-- count(*)
select group_concat(id),group_concat(password),group_concat(username)as '用户名' ,max(age)as '最大年龄',avg(age)as '平均年龄',count(*),sum(age) as '年龄总和' from user1group by sex;
-- having语句对分组结果进行二次筛选 (where是第一次筛选，having语句是二次筛选)，使用的条件是分组，必须要有group by使用having语句才有意义，才有发挥作用
select id,username,password,count(id)from user1 where id>2 group by sex having count(*)>1;
+----+-----------+----------+-----------+
| id | username | password | count(id) |
+----+-----------+----------+-----------+
| 3 | 以西结 | king | 3 |
| 4 | 卢西尔 | weapon | 2 |
+----+-----------+----------+-----------+
g.)正则表达式查询:
-- 正则表达式查询
-- regexp'匹配方式'
-- ^匹配字符开始的部分
-- $匹配字符串结尾的部分
-- .代表字符串中额任意一个字符，包括回车和换行
-- [字符集合]匹配字符集合中的任意一个字符
-- [^字符集合] 匹配除了字符集合以外的任意一个字符
-- s1|s2|s3代表s1、s2、s3中的任意一个字符串
-- *代表0个1个或者多个其前的字符
-- +代表1个或者多个其前的字符
-- string{n} 字符串出现n此
-- 字符串{m,n}字符串至少出现m次，最多出现n次 注意是大括号!!!!
-- 正则匹配:
select 'cjgong' regexp'^c' as 是否以c开头;
select 'cjgong' regexp'g$' as 是否以g结尾;
select 'cjgong' regexp'c....g' as 4个省略;
select 'cjgong' regexp'[abc]' as '是否含有abc字符';
select 'cjgong' regexp'[a-z]' as '是否含有a-z的字符';
select 'cjgong' regexp'[^abc]' as '是否含有abc字符以外的字符';
select 'cjgong' regexp'[^a-za-z0-9]' as '是否不包括a-za-z0-9的字符';
select 'cjgong' regexp'a*g' as 'g之前是否有0个或1个或者多个a';
select 'cjgong' regexp'a+g' as 'g之前是否有至少1个a';
select * from user1where username regexp '^瑞'
select * from user1where username regexp '尔$'
select * from user1where username regexp '摩.'
select * from user1where password regexp '[a-f]'
select * from user1where username regexp '以|尔|根'
select * from user1where username regexp '以*'
+----+-----------+----------+------+------+
| id | username | password | age | sex |
+----+-----------+----------+------+------+
| 3 | 以西结 | king | 35 | 男 |
+----+-----------+----------+------+------+
select * from user1where username regexp '以西结*'
select * from user1where password regexp 'king+'
select * from user1where username regexp '尔{1}'
+----+-----------+-----------+------+------+
| id | username | password | age | sex |
+----+-----------+-----------+------+------+
| 1 | 卡罗尔 | 卡罗尔 | 16 | 男 |
| 4 | 卢西尔 | weapon | 16 | 女 |
+----+-----------+-----------+------+------+
select * from user1where username regexp '尔{1,2}'
h.)字符串函数库
-- 字符串函数库
-- char_length(s)返回字符串的字符数
-- length(s)返回字符串的长度 ---------- 比如中文的字符数是1，但是字符串长度是3(utf_8情况下)
-- concat(s1,s2)将字符串合并为一个字符串 -------如果连接null的话结果是null
select concat('hello',' world');
-- concat_ws(x,s1,s2...)以指定分隔符合并一个字符串 --------如果分隔符是null 那么结果一定是null，但是如果被连接的字符串中有null，那么则没有关系
select concat_ws('->','a','b','c');
+-----------------------------+
| concat_ws('->','a','b','c') |
+-----------------------------+
| a->b->c |
+-----------------------------+
-- upper(s)/ucase(s) 字符串大写 lower(s)/lcase(s) 字符串小写
select ucase('wo bian daxie '),lcase('wo bian xiaoxie');
+-------------------------+--------------------------+
| ucase('wo bian daxie ') | lcase('wo bian xiaoxie') |
+-------------------------+--------------------------+
| wo bian daxie | wo bian xiaoxie |
+-------------------------+--------------------------+
-- left(s,n)返回s中的前n个字符 right(s,n) 返回s中的后n个字符
select left('123abcd',5),right('123abcd',5);
-- lpad(s1,len,s2) / rpad(s1,len,s2) 将字符串s1用s2填充到指定的len
select lpad('a',10,'abcdef');
+-----------------------+
| lpad('a',10,'abcdef') |
+-----------------------+
| abcdefabca |
+-----------------------+
-- ltrim(s)/rtrim(s)/trim(s)去掉字符串s中的空格 ltrim(s)去掉左边空格，rtrim(s)去掉右边空格
select concat('_',trim(' abc '),' ') ,concat('_',ltrim(' abc '),'_'),concat('_',rtrim(' abc '),'_');
+-------------------------------+--------------------------------+--------------------------------+
| concat('_',trim(' abc '),' ') | concat('_',ltrim(' abc '),'_') | concat('_',rtrim(' abc '),'_') |
+-------------------------------+--------------------------------+--------------------------------+
| _abc | _abc _ | _ abc_ |
+-------------------------------+--------------------------------+--------------------------------+
-- trim(s1 from s) 把s1从s中去掉
select trim('a' from 'abcbca');
mysql> select trim('a' from 'abcbca');
+-------------------------+
| trim('a' from 'abcbca') |
+-------------------------+
| bcbc |
+-------------------------+
-- reapeat(s,n) 重复字符串n次
select repeat('holo',6);
+--------------------------+
| repeat('holo',6) |
+--------------------------+
| holoholoholoholoholoholo |
+--------------------------+
-- space(n) 返回n个空格
select concat('—',space(6),'_');
+----------------------------+
| concat('—',space(6),'_') |
+----------------------------+
| — _ |
+----------------------------+
-- replace(s,s1,s2) 在s中用s2替换s1 区分大小写 a和a是不同的
select replace('abcdeedcba','ab' ,'xx');
+----------------------------------+
| replace('abcdeedcba','ab' ,'xx') |
+----------------------------------+
| xxcdeedcba |
+----------------------------------+
## 小写
select replace('abcdeedcba','ab' ,'xx');
## 无法替换
+----------------------------------+
| replace('abcdeedcba','ab' ,'xx') |
+----------------------------------+
| abcdeedcba |
+----------------------------------+
-- char_length(s)返回字符串的字符数
-- length(s)返回字符串的长度 ---------- 比如中文的字符数是1，但是字符串长度是3(utf_8情况下)
-- concat(s1,s2)将字符串合并为一个字符串 -------如果连接null的话结果是null
-- concat_ws(x,s1,s2...)以指定分隔符合并一个字符串 --------如果分隔符是null 那么结果一定是null，但是如果被连接的字符串中有null，那么则没有关系
-- upper(s)/ucase(s) 字符串大写 lower(s)/lcase(s) 字符串小写
-- left(s,n)返回s中的前n个字符 right(s,n) 返回s中的后n个字符
-- lpad(s1,len,s2) / rpad(s1,len,s2) 将字符串s1用s2填充到指定的len
-- ltrim(s)/rtrim(s)/trim(s)去掉字符串s中的空格 ltrim(s)去掉左边空格，rtrim(s)去掉右边空格
-- trim(s1 from s) 把s1从s中去掉
-- reapeat(s,n) 重复字符串n次
-- space(n) 返回n个空格
-- replace(s,s1,s2) 在s中用s2替换s1 区分大小写 a和a是不同的
-- strcmp(s1,s2)比较2个字符串是否相同 返回0代表相同
-- substring(s,n,len) 截取字符串
-- reverse(s) 反转字符串
-- elt(n,s1,s2...)返回指定位置n的字符串
-- 日期时间函数没有什么好说的，需要的时候再看
-- 条件函数
-- if(expr ,v1,v2) 如果表达式expr成立，返回结果v1;否则返回v2
-- ifnull(v1,v2) 如果v1不为空，则显示v1的值，否则v2
-- case when exp1 then v1[when exp2 then v2]...[else vn] end --case表示函数开始,end表示函数结果，如果表达式exp1成立，返回v1,如果表达式exp2成立，返回v2，以此类推其他返回vn
select * ,if(age>18,'成年' ,'未成年')from user1; -- 注意,逗号别忘记了
+----+-----------+--------------+------+--------+----------------------------------+
| id | username | password | age | sex | if(age>18,'成年' ,'未成年') |
+----+-----------+--------------+------+--------+----------------------------------+
| 1 | 卡罗尔 | 卡罗尔 | 16 | 男 | 未成年 |
| 2 | 黑人 | 黑人 | 16 | 保密 | 未成年 |
| 3 | 以西结 | king | 35 | 男 | 成年 |
| 4 | 卢西尔 | weapon | 16 | 女 | 未成年 |
| 5 | 瑞克 | leader | 16 | 男 | 未成年 |
| 6 | 刀女 | 强力输出 | 16 | 女 | 未成年 |
| 7 | 摩根 | 反省 | null | 男 | 未成年 |
+----+-----------+--------------+------+--------+----------------------------------+
select *,ifnull(age,'秘密')from user1;
+----+-----------+--------------+------+--------+----------------------+
| id | username | password | age | sex | ifnull(age,'秘密') |
+----+-----------+--------------+------+--------+----------------------+
| 1 | 卡罗尔 | 卡罗尔 | 16 | 男 | 16 |
| 2 | 黑人 | 黑人 | 16 | 保密 | 16 |
| 3 | 以西结 | king | 35 | 男 | 35 |
| 4 | 卢西尔 | weapon | 16 | 女 | 16 |
| 5 | 瑞克 | leader | 16 | 男 | 16 |
| 6 | 刀女 | 强力输出 | 16 | 女 | 16 |
| 7 | 摩根 | 反省 | null | 男 | 秘密 |
+----+-----------+--------------+------+--------+----------------------+
select * ,case when id<4then '前三甲' when age>16then '获奖' end from user1;-- 一个case就可以
+----+-----------+--------------+------+--------+-----------------------------------------------------------------+
| id | username | password | age | sex | case when id<4 then '前三甲' when age>16then '获奖' end |
+----+-----------+--------------+------+--------+-----------------------------------------------------------------+
| 1 | 卡罗尔 | 卡罗尔 | 16 | 男 | 前三甲 |
| 2 | 黑人 | 黑人 | 16 | 保密 | 前三甲 |
| 3 | 以西结 | king | 35 | 男 | 前三甲 |
| 4 | 卢西尔 | weapon | 16 | 女 | null |
| 5 | 瑞克 | leader | 16 | 男 | null |
| 6 | 刀女 | 强力输出 | 16 | 女 | null |
| 7 | 摩根 | 反省 | null | 男 |null |
+----+-----------+--------------+------+--------+-----------------------------------------------------------------+
-- 函数 select+以下
version() 返回数据库的版本号
connection_id() 返回服务器的连接数
database() 返回当前数据库名
user() 返回当前用户
durrent_user() 返回当前用户
charset(str) 返回str的字符
collation(str) 返回字符串str的校验字符集
last_insert_id()返回最近生成的auto_ increment
-- 函数
-- md5(str) 信息摘要算法 返回32位的字符串
-- password(str) 密码算法
-- 其他函数
-- conv(x,f1,f2)将x从f1转换为f2进制
-- get_lock(name,time) 定义锁
-- is_free_lock(name)看锁是否在使用
i.)索引
索引的分类:普通索引 、唯一索引、全文索引、单列索引、多列索引、空间索引
以下情况适合创建索引:
经常被查询的字段，即在where子句中出现的字段
在分组的字段，即在group by子句中出现的字段
存在依赖关系的子表和父表之间的联合查询，即主键或外键字段
设置唯一完整性约束的字段
不适合创建索引:
在查询中很少被使用的字段
拥有许多重复值的字段
创建索引的三种方式:
在创建表时创建普通索引:
create table cms_news(
属性名数据类型....
index|key [索引名] (属性名1 [(长度)] [asc|desc])
);
在已经存在的表上创建普通索引
create index 索引名on tbl_name(字段名)
通过sql语句alter table来创建普通索引
alter table tbl_name
add index|key 索引名(属性名,...)
根据创建索引的方式，可以分为手动索引和自动索引
当你把某个字段设置主键或者唯一完整性约束的时候，系统就会自动创建关联该字段的唯一索引。
全文索引:
主要关联在数据类型为char、varchar、和text的字段上，以便能够能加快速的查询数据量较大的字符串类型的字段。
fulltext index
多列索引:
多个字段，但是只有查询条件中使用了所关联字段中的第一个是字段，多列索引才会被使用。
user1 两个字段 a b
index mul_index(a,b)
select * from user1 where a=.....;
删除索引:
drop index index_name on tbl_name
j.)位运算符:
& 按位与
| 按位或
~ 按位取反
^ 按位异或
<<按位左移
>>按位右移
按位的操作如果不加上bin(x1&x2) hex(x1^x2)操作，则是把十进制转换成二进制进行操作，然后再把二进制转换成十进制进行显示。
bin()把结果显示为二进制