MySQL学习笔记六：表的创建及管理

一、创建表

需要信息：表名，表字段名，表字段的定义

create table table_name 列定义 选项;

create table table_name like old_table_name; --like:包括旧表的结构+信息+索引

create table table_name select * from old_table_name; --包括旧表的结构+信息

create table studyt1
(
    id int(20) unsigned auto_increment not null,
    name varchar(20) not null,
    jobdate date,
    primary key (id)
) engine=innodb default charset=utf8;

show create table studyt1;

二、查询表

show tables;

show tables from db_name;

show tables like '关键字';

show talbes form db_name like '关键字';

show tables;
show tables from test;
show tables like 'bl%';
show tables from test like 'bl%';

三、查看表内容

select * from study01;
select * from test.study01;
select id01,id02 from test.study01 limit 2;

四、表的增删改查

查数据库是否是自动commit

show variables like '%autocommit%';

测试表和数据

create table study11 (id int(3),name varchar(12),sex varchar(6));
create table study12 (id int(3),name varchar(12),age int(5));

insert into study11 (id,name,sex) values 
(1,'study01','男'),
(2,'study02','男'),
(3,'study03','女'),
(4,'study04','女'),
(5,'study05','女');

insert into study12 (id,name,age) values 
(1,'study01',20),
(2,'study02',21),
(3,'study03',18),
(4,'study04',19),
(5,'study05',28);

4.1、insert

语法：insert into table_name (表字段) values (值列表);

/*方法1*/
insert into study11 (id,name,sex) values (6,'study06','男');/*方法2*/
insert into study11 values (7,'study07','男');
/*方法3*/
insert into study11 set id=8,name='study06',sex='男';
/*方法4*/
insert into study1 values (1,'study01',now(),20);/*方法5*/
insert into study1 values (2,'study02',default,20);
/*方法6*/
create table study13 select * from study11;truncate table study13;
insert into study13 select * from study11;

4.2、delete

语法：delete from 表名 [where 条件] [order by] [limit row_count];

delete from study13 where id=1;
delete from study13 limit 2;

4.3、update

语法：update 表名 set 列名=值 where 条件;

update study13 set name='study11',sex='女' where id=1;

4.4、select

语法：select 字段/表名 from 表名/视图名 where 查询条件;

查询条件：

1)where 条件

2)group by 分组

3)having 分组后再聚合

4)limit 限制多少行显示

5)order by [asc|desc] 排序，升|降

4.4.1、列连接

select name,concat(name,'-',sex) as '姓名+性别' from study11;

4.4.2、别名 as/也可以省略

select a.* from study11 a,study12 b where a.name=b.name;

4.4.3、虚拟表dual

select now() from dual;

4.4.4、SQL语句注释方式

1）语句前注释：#

#select now() from dual;

2）语句后注释：--
select now() from dual; --查当前系统时间

3）多行注释：/**/

/*select now() from dual;*/

4.4.5、常用的运算符

=：等于

>：大于

<：小于

>=：大于等于

<=：小于等于

<>：不等于

!=：不等于

is null：为null

is not null：不为null

[not]like：模糊查询

[not]between and：在什么范围内

[not]in：在什么范围值内

select * from study12 where age=20;
select * from study12 where age<>20;
select * from study12 where age>20;
select * from study12 where age>=20;
select * from study12 where age<20;
select * from study12 where age<=20;
select * from study12 where age between 18 and 20;
select * from study12 where age not between 18 and 20;
select * from study12 where age>=18 and age<=20;
select * from study12 where age>=18 && age<=20;
select * from study12 where age in (18,19,28);
select * from study12 where age not in (18,19,28);
select * from study12 where name like 'study%';
select * from study12 where name not like '%005%';

4.4.6、逻辑运算

非：not

与：and &&

或：or

异或：xor

select null is not not null,null is null;
select null<=>null,10<=>null;

4.4.7、组合

select * from study12 where name='study01' and age=20;
select * from study12 where name='study01' or age=21;

4.4.8、like

select * from study12 where name like 'study%';

4.4.9、查询分组与排序

group by 分组

group by 列 {asc升序|desc降序},{with rollup} 组内聚合计算

select left(name,4),group_concat(name) name from study12 group by left(name,4);

4.4.10、limit

限制返回的行数

select * from study12 order by age desc limit 1;

4.4.11、distinct

去除重复记录

select distinct left(name,4) name from study12;

4.4.12、union

无重并集：把多个结果组合并去重，再以第1列的结果进行升序排序。

select name from study11 union select name from study12;

4.4.13、union all

有重并集：把多个结果组合不去重

select name from study11 union all select name from study12;

4.4.14、for update

会锁表(生产环境不要轻易用)

select * from study11 for update;