mysql常用的基本操作
创建新的用户
用户的信息都保存在数据库的库名为mysql的user表中.而root和123456是默认创建的用户.
如果我们要创建新的用户,也会保存到当前的user表中.
数据库常见操作
数据库中常见的数据类型
bit[(M)]
二进制位（101001），m表示二进制位的长度（1-64），默认m＝1
tinyint[(m)] [unsigned] [zerofill]
小整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-128 ～ 127.
无符号：
～ 255
特别的： MySQL中无布尔值，使用tinyint(1)构造。
int[(m)][unsigned][zerofill]
整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-2147483648 ～ 2147483647
无符号：
～ 4294967295
特别的：整数类型中的m仅用于显示，对存储范围无限制。例如： int(5),当插入数据2时，select 时数据显示为： 00002
bigint[(m)][unsigned][zerofill]
大整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-9223372036854775808 ～ 9223372036854775807
无符号：
～ 18446744073709551615
decimal[(m[,d])] [unsigned] [zerofill]
准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
特别的：对于精确数值计算时需要用此类型
decaimal能够存储精确值的原因在于其内部按照字符串存储。
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
无符号：
-3.402823466E+38 to -1.175494351E-38,
1.175494351E-38 to 3.402823466E+38
有符号：
1.175494351E-38 to 3.402823466E+38
**** 数值越大，越不准确 ****
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
无符号：
-1.7976931348623157E+308 to -2.2250738585072014E-308
2.2250738585072014E-308 to 1.7976931348623157E+308
有符号：
2.2250738585072014E-308 to 1.7976931348623157E+308
**** 数值越大，越不准确 ****
char (m)
char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。
PS: 即使数据小于m长度，也会占用m长度
varchar(m)
varchars数据类型用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的
字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。
注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，
有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，
以求达到最佳的平衡
text
text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。
mediumtext
A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.
longtext
A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.
enum
枚举类型，
An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
示例：
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
set
集合类型
A SET column can have a maximum of 64 distinct members.
示例：
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
DATE
YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
HH:MM:SS（'-838:59:59'/'838:59:59'）
YEAR
YYYY（1901/2155）
DATETIME
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
修改表的列(字段)
修改表(修改列,以及列的类型和约束):
1. 添加列:
alter(不是alert) table 表名 add 列名 类型;
alter(不是alert) table t1 add age int default 0;(默认添加到最后一列)
alter(不是alert) table t1 add address char(12) first; (添加到第一列)
alter(不是alert) table t1 add phone after id; (在某个列的后面添加一列)
2. 删除列
alter table 表名 drop 列名;
alter table t1 drop phone;
3. 修改列
alter table 表名 modify column 列名 类型; # 只修改类型,使用modify'
alter table 表名 change column 原来的列名 新列名 类型; # 列名和类型同时修改使用change
alter table t1 change column name my_name varchar(20);
4. 添加主键
alter table 表名 add primary key(列名)
alter table t1 add primary key(id);
5.删除主键
alter table 表名 drop primary key(id);
alter table 表名 modify 列名 int ,drop primary key;
alter table t1 drop primary key ;
alter table t1 modify id int ,drop primary key;
6.添加外键
alter table 从表 add constraint 从键名称 foreign_key 从表(外键字段) references 主表(主键字段);
删除外键:
alter table 表名 drop foreign_key 外键名称
7.修改默认值:
alter table t1 alter age set default 1000;
删除默认值
alter table t1 alter age drop default;
表内容操作
1. 插入数据(insert into 表名(字段,字段) values(值,值))
insert into tb1(nid,name) values(1,'fioman');
2. 查询操作(select 字段,字段 表名(字段,字段) values(值,值))
select * from tb1;
3. 增
insert into 表名(列名,列名) values(值,值)
insert into 表名(列名,列名) values(值,值),(值,值)
insert into 表名(列名,列名) select (列名,列名) from 表
4. 删
delete from 表
delete from 表 where id = 1 and name = 'alex'
5. 改
update 表 set name = 'jingjing' where id > 1;
6. 条件
select * from 表 where id > 1 and name != 'alex' and age = 18;
select * from 表 where id between 5 and 16;
select * from 表 where id not in (11,22,33)
select * from 表 where id in (select nid from 表2)
7.通配符
select * from 表 where name like 'ale%' ale开头的所有的字符串(%表示任意多个字符)
select * from 表 where name like 'ale_' ale开头的后面加一个字符(一个字符)
8.限制
select * from 表 limit 5; 前5行
select * from 表 limit 4,5; 从第四行开始(不包括) 5行到9行的数据
select * from 表 limit 5 offset 4 - 从第四行开始的5行
9.排序
select * from 表 order by 列 asc 升序
select * from 表 order by 列 desc 降序
select * from 表 order by 列1 desc,列2 asc
10.分组
select num from 表 group by num;
select num ,nid from 表 group by num,nid;
select num nid from 表 where nid > 10 group by num,nid order nid desc;
select num nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid;
select num from 表 group by num having max(id) > 10
特别的group by 必须在where之后,order by 之前
11.连表
无对应关系则不显示
select A.num ,A.name B.name from A,B where A.nid=B.nid
无对应关系则不显示
select A.num,A.name,B.name from A inner join B on A.nid=B.nid
A表所有显示,如果B中午对应关系,则值为null
select A.num,A.name,B.name from A left join B on A.nid = B.nid
B表所有显示,如果B中午对应关系,则值为null
select A.num,A.name,B.name from A right join B on A.nid = B.nid
12.组合
组合,自动处理重合
select nickname from A union select name from B
组合,不处理重合
select nickname from A union all select name from B
理解MySql的内连接,左外连接,右外连接,全连接
首先创建两张表a_table和b_table,关联字段a_talbe.a_id和b_table.b_id
CREATE TABLE `a_table` (
`a_id` int(11) DEFAULT NULL,
`a_name` varchar(10) DEFAULT NULL,
`a_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `b_table` (
`b_id` int(11) DEFAULT NULL,
`b_name` varchar(10) DEFAULT NULL,
`b_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
表测试数据
内连接
关键字: inner join on 是显示的内连接,同时用where子句的隐私的内连接.它的重点是只查找匹配的行.
语句
select * from a_table a inner join b_table b where a.a_id=b.b_id
左连接(左外连接)
关键字: left join 表 on 条件/ left outer join on
语句
select * from a_table a left join b_table on a.a_id=b.b_id
说明: 左外连接左表中的内容会全部显示出来,而右表中的内容只显示匹配的内容,而至于没有匹配的内容一律用null来填充
右连接(右外连接)
关键字:right join 表 on 条件/rigth outer join 表 on
语句:
select * from a_table a right join b_table b on a.a_id=b.b_id;
right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。
与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。
交叉连接
SQL语句查询的基本原理
单表查询
根据where条件过滤掉表中的记录,形成中间表(这个中间表用户是不可见的).然后根据select的选择列进行返回最终结果.
两表连接查询
对两表求积（笛卡尔积）并用ON条件和连接连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。
多表连接查询
先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。
理解SQL查询的过程是进行SQL优化的理论依据。
on后面的条件和where条件的区别
ON条件：是过滤两个链接表笛卡尔积形成中间表的约束条件。
WHERE条件：在有ON条件的SELECT语句中是过滤中间表的约束条件。在没有ON的单表查询中，是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。
从这里可以看出，将WHERE条件移入ON后面是不恰当的。推荐的做法是：
ON只进行连接操作，WHERE只过滤中间表的记录。
数据库常用的操作:
1.创建数据库:
create databasedb_name;
2.创建并设置字符编码
create databasedb_name character set utf8;
3.查看数据库的字符集编码:
show variables like 'character_set_database'
4.修改字符集编码
alter db_name character set utf8
数据表常见的操作:
1.创建一个表
create table tb_name(id int,name varchar(20)) ;
注意在创建表的时候,不要添加记录,添加记录用insert into
2.删除表
drop table tb_name;
3插入数据到表中
insert into tb_name(id,name) values(1,'a'),(2,'b')
查询表
select * from tb_name where 查询条件
添加一个字段(列)
alter table tb_name add age int not null default 10;
删除列
alter table tb_name drop column age;
修改某一列的字段类型
alter table tb_name modify age varchar(50)
更新某一列的数据
update table tb_name set age = 100 where id =1;
约束
1) 约束的种类
primary key(主键约束 唯一且不为null)
default(默认值约束)
not null (非空约束)
unique(唯一约束,可以为null值)
foreign key(外键约束)
check(检查约束)
2) 添加约束
alter table tb_name add primary key(id)
alter table tb_name add unique(test_student)
alter table tb_name add foreign key(id)
3)删除约束
alter table tb_name drop primary key
常用的查询语句
1.简单查询
无条件查询
select * from tb_name; select col1,col2,... from tb_name
条件查询
select * from tb_name where 查询条件;
排序查询
select * from tb_name where 条件 order by 字段 asc/desc desc降序, asc升序.默认是升序asc
例如:查询出及格的学生,并按照从高到低进行排序:
select * from students where score >= 60 order_by score desc;
模糊查询
使用关键字like主要使用%,下划线,[]三个字符. % 表示匹配0个或多个字符(通配符),下划线表示匹配一个字符.[]匹配它里面的一个,类似正则.
例一: 查询名字中姓张的
select * from person where name like '张%';
例二:查询姓张的且只有两个字的
select * from person where name like '张_';
例三:查询姓氏为张,王,李的人
select * from person where name like '[张王李]%'
分组查询:
select * from tb_name group by 列名.关键字group by将统计列中相同的数据进行分组.
例一:查询学生中的每个分数有多少人,就是按分数进行分组.有相同的分数将进行合并
select score,count(*) from student group by score;
分组常用的函数
1）max：求最大值 例： select s_name,max(math_score) from student group by s_name 查询数学成绩最高的学生姓名
select s_name,max(math_score) from student group by s_name
（2）min：求最小值 例： select s_name，min(math_score) from student group by s_name 查询数学成绩最低的学生姓名
select s_name，min(math_score) from student group by s_name
（3）avg：求平均值 例： select class_id，avg(math_score) from student group by class_id 查询每个班的平均数学成绩
select class_id，avg(math_score) from student group by class_id
（4）sum：求总数和 例： select sum(s_id) from student 查询表中一共有多少学生
select sum(s_id) from student
（5）count：求总行数
having的用法:筛选成组后的各种数据,它可以筛选出表中没有的数据作为查询条件
比如:
select s_name,sum(s_socre) from student group_by s_name having sum(s_socre) > 600
查询总成绩大于600分的学生,当我们表没有总分这个记录.
只有每科的成绩,这时就可以使用having了,where就不能来筛选总成绩大于600的学生了.
having和where的区别?
having: 是对查询的结果中的列发挥作用,一般配合group by来使用,并且这个列可以不是表中的字段.
where: 是针对表中的列进行查询,直接在原表上进行操作.而having是对查询的结果进行判断,筛选出满足条件的数据记录.
limit:
主要用来分页,limit n,m表示从n+1开始取m条数据
比如:从去结果中第三条到低5条数据
select * from tb_name limit 2,5
简单的多表查询
select table1.* ,table2.* from table1,table2 where 查询条件.
子查询和连接查询
1.where子查询:
select s_name,s_score from student where s_score in (select s_score from student where s_score >= 60)
查询成绩及格的学生,后面括号里可以放子查询,也可以放已知的数据.
