SQL Learning 1: a simple primer

Create a database

• Create database name [library option];
• Create a database

    create database mydatas charset utf8;

• View Database

    show databases;

• If you need Chinese

  set names gbk;

• View Database

    show databases like ‘my%‘; -- 查看以my开始的数据库

• View Database Creation statements

    show create database mydatas;

• Deleting a database

    drop dababase mydatas;

• Working with databases

    use mydatas;

Create a table

• Create a table

1. Specify Database Creation table

create table if not exists  mydatas.table1(    id int not null primary key,    name varchar(20) not null)charset utf8;

1. After using the database use Mydatas

create table if not exists tabke2(    id int not null primary key auto_increment,    age int not null)charset utf8;

• View all Tables

show tables;

• View the creation structure of a table

show create table 表名;show create table table1;

• View table starts with TA

show tables like ‘ta%‘;

• View table Structure

desc 表名    desc table1;describe 表名    describe table1;    columnsfrom 表名    columnsfrom table1;

• Renaming a table

renametableto table2;

• Modify table Options-Character Set

altertable table1 charset = GBK;

• Add a UID to Table1 in the first place

altertableaddcolumnuidintfirst;        

• After adding a field to a specific field

altertableaddcolumnnumberchar(11afterid;

• Modify the properties of a field and place it after a field

altertablemodifynumberintafteruid;

• Modify table field name

altertablechange 需要修改的字段名 新的字段名 字段类型(必须存在);altertablechangeuidvarchar(50);

• Delete a field from a table

altertabledrop 字段名;altertabledrop uuid;

• Delete a data table

table 表名;droptable table2;

• Inserting data

insertintovalues(对应的字段列表值);-- 省略自增长的idinsertintovalues(‘nordon‘,22),(‘wy‘,21);-- 使用default、null进行传递字段列表值, id会自动增加insertintovalues(null,‘张三‘,22),(default,‘李欧尚‘,21);

• View data

-- select 字段名 from 表名 where 条件;selectfromwhereid1;

• Update data

-- update 表名 set 字段 = 新值 where 条件updateset‘王耀‘whereid2;

• Delete data

-- delete from 表名 whrer 条件deletefromwhereid5;

Small knowledge

1. To view all character sets

characterset;

1. View the server default character set for external processing

like‘character_set%‘;-- 修改服务器认为的客户端数据的字符集为GBKset character_set_client = gbk;-- 修改服务器给定数据的字符集为GBKset character_set_results = gbk;-- 快捷设置字符集set names gbk;

1. Proofing Sets

--View all proofing setsShow collation;--Create a table with a different proofing setCreate TableMy_collate_bin (nameChar(1)) CharSet UTF8 collate utf8_bin;Create TableMY_COLLATE_CI (nameChar(1)) CharSet UTF8 collate utf8_general_ci;--Inserting dataInsert  intoMy_collate_binValues(' A '),(' A '),(' B '),(' B ');Insert  intoMy_collate_ciValues(' A '),(' A '),(' B '),(' B ');--Sort FindSelect* fromMy_collate_binOrder  byNameSelect* fromMy_collate_ciOrder  byName--Modify the proofing set after having dataAlter TableMy_collate_ci collate = Utf8_bin;Alter TableMy_collate_ci collate = utf8_general_ci;

SQL Learning 1: a simple primer