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;

標籤：MySQL

上一篇：MySQL學習筆記五：資料型別

下一篇：in多值優化