登陆

mysql -u root -p

显示所有数据库信息

show databases

创建数据库的同时记得同步设置其编码格式, 否则默认为utf8

create database <数据库名> character set utf8;

查看数据库的编码方式

show variables like "%char%"

切换数据库

use 数据库名

创建数据表

1
2
3
4

create table [if not exists] table_name (
    column_name data_type
    ...
)

查看数据表

show tables

show tables from mysql: 查看mysql中所有的数据表

查看表的列

show cloumns from 表名

查看表结构

desc 表名

向数据表中写入记录

insert [into] 表名 [列名列表] values(值)

当需要为列的部分数据写入记录的时候,i 需要在表名中指定需要插入的对应列名称

insert tb1(username, age) values('Changer', 18);

查看数据记录

select 表达式 from 表名

在tb1表中查看所有记录: select * from tb1

设置字段的值为空值或者非空

在创建表的时候设置表的列的属性为not null或 为null, 这两个是成对出现的

null / not null

设置字段的值自增

设置字段的值自增时, 每个表只能设置一个, 并且需要将该值设置为主键(主键约束)

auto_increment

约束

唯一约束

唯一约束可以保证记录的唯一性

唯一约束的字段可以为空值

每张数据表可以有多个唯一约束, 但是只能有一个主键约束

unique key

默认约束

也就是默认值

当插入记录时, 如果没有明确为字段赋值, 则自动服务默认值

default 默认值

约束保证数据的完整性和一致性

约束分为表级约束和列级约束

约束类型包括: 非空约束, 主键约束, 唯一约束, 默认约束, 外键约束

外键约束

保证数据的唯一性, 实现一对一或一对多的关系

外键约束的要求:

1. 父表和子表必须使用相同的存储引擎(InnoDB)
2. 外键列和参照列必须具有相似的数据类型, 其中数字的长度或是否有符号位必须相同, 而字符的长度则可以不同
3. 外键列和参照列必须创建索引, 如果外键列不存在索引的话, mysql将会自动创建索引

foreign key (当前表的字段名) references 关联的表 (关联的表字段)

修改数据表

添加单列(为表结构中插入一个列)

1

alter table tbl_name add col_name col_type [first / after col_name];

插入单列, 添加时指定该列的类型, 并指定其位于首行(first) 或者在某行之后(after)

删除单列

1

aleter table tbl_name drop col_name;

删除单列上面这一整列的内容

删除和增加列的主键约束, 外键约束

略

修改列定义

1

alter table tbl_name modify col_name  col_type [first / after col_name];

注意, 修改列定义的时候, 由大修改为小的, 有可能会造成数据丢失

修改列名称

1

alter table tbl_name change old_col_name new_col_name new_col_type [first / after col_name];

修改数据表名

1

alter  table tbl_name rename new_tbl_name;

插入记录

对于插入内容中的自增数据, 当我们传一个null或者传一个default的时候, 其内容会产生一个自增的数据, 但是不能不传, 不传的话会产生报错

1
2
3
4
5
6
7

create table users(
    id smallint unsigned primary key auto_increment,
    username varchar(20) not null,
    password varchar(32) not null,
    age tinyint unsigned not null,
    sex boolean
);

对于上面的结构, 我们进行数据插入的时候, 如果需要其内容自增, 可以直接使用null或者传一个default

1

insert users values(null, 'John', '123', 15, 1);

上段命令用于插入一个自增的数据, username为John, password为123, age为15, 性别为1

所以同样的, 我们是用default替代null, 也能得到相同的结果

对于tinyint字段, 我们可以使用数学表达式来进行输入, mysql将会把内容计算之后插入

1

insert users values(null, 'John', '345', 3*7-5, 0);

通过在插入前进行加密, 可以直接在数据库中插入对应数据的加密值

1

insert users values(null, 'Changer', md5('123'), 18, 0);

set-select

与前一种方式的区别在于, 此方法可以使用子查询

1

insert tb_name set key_1='value_1' key_2='value2';

另外, 还可以使用insert select语句来将查询结果插入到指定数据表

1

insert tb_name [col_name] select

单表更新记录

1

update [low_primarity] [ignore] table_reference set col_name1={expr1|default}[,col_name2={expr2|default}]... [where where_condition]

例子:

1

update users set age = age + 5;

为没个数据的年龄字段全部加上5

1

update users set age = age - id, sex = 0;

为没个数据的年龄等于源年龄减id, 所有sex都是0

1

update users set age = age + 10 where id % 2 = 0;

为所有id能被2整除的字段的年龄加10

单表删除操作

1

delete from table_name [where where_condition];

删除数据表中的内容, 不加where时会被全部删除

删除行操作

1

DELETE FROM 表名称 WHERE 列名称 = 值

注, 当有外键约束在其中的时候, 该行是无法删除的

select语句

• select中字段名的出现顺序将会影响输出的结果顺序
• select中的字段别名将会影响输出的结果名

1

select users.id,users.username from users;

可以在输出的select选择时加上前缀, 以免出现重复的情况

1

select id as userId, username as uname from users;

可以将输出的结果的列名设置一个别名来进行输出

group by 分组

将select中的查询条件进行分组

1

select sex from users group by sex;

group by也允许在后面传递一个数字, 表示以select所选择的第几个字作为分组条件

1

select sex from users group by 1;

得到的结果与上一表达式相同

但是需要注意的是, group by后面跟的条件是不能设置为之前select中没有的内容的, 如果需要设置为之前没有的内容, 应该使用函数的方式进行书写

1

select sex from users group by 1 having count(id) <= 2;

order by 对结果进行排序

通过在select语句后加上order by的方式可以实现输出内容的顺序排序, 默认为由低向高排序, 可加desc来进行由高向低排序

1

select * from users order by age desc;

对于输出排序结果相同的情况, 我们则需要增加一个排序条件, 排序条件可直接以逗号分隔

1

select * from users order by age desc, id;

上段语句的意思是, 显示所有users表中内容, 其中输出结果以age从高向低排列, 当age相同时, 以id排序, 从低向高排列

限制查询limit

可以进行限制, 每次返回几条数据, 从第几条开始

1

select * from users limit 0, 2;

以上表示返回两条数据, 从第0条开始

需要注意的是, 每次返回会以表中的默认排序顺序进行排序

创建新表, 并将原来表的内容输入到新表

1

insert test(username) select username from users where age >= 20;

上个语句的意思是, 为test中的username字段, 插入users表中age大于等于20的所有内容

子查询

求平均值

1

select avg(goods_price) from tdb_goods;

求平均值, 并将值省略小数点后两位

1

select round(avg(goods_price), 2) from tdb_goods;

输出符合条件的, 需要的内容

1

selct goods_id,goods_name,goods_price from tdb_goods where goods_price >= 5636;

上面的查询语句输出goods_id, goods_name, goods_price三个字段, 输出的条件是, goods_price的值大于等于5636

所以同样的, 因为上面的方法我们是分开两个方法来执行了, 当我们需要作为一条语句执行的时候, 可以将两条语句合并起来

1

select goods_id,goods_name,goods_price from tdb_goods where goods_price >= (select round(avg(goods_price),2) from tdb_goods);

使用any, some, all来修饰的比较运算符

联合使用select和insert语句将原表的分类导入到另一张表

1

insert ttb_goods_cates(cate_name) selct goods_cate from tdb_goods order by goods_cate;

上个语句的意思是, 将tdb_goods表中的goods_cate的分类导出到ttb_goods_cates表中的cate_name字段中去

表连接

连接两表, 并将原表中的各种分类导出到另一张表之后, 我们暂时还没有办法去将原表的内容与导出的表的分类的对应id进行匹配, 那么可以使用多表更新的方式, 使两表产生一个连接的关系

1

update tdb_goods inner join tdb_goods_cates on goods_cate = cate_name set goods_cate = cate_id;

上个语句的意思是, 将tdb_goods这个表, 设置tdb_goods_cates作为其子表, 并更新goods_cate的内容为cate_id前提是good_cate = cate_name

创建表的同时将内容插入数据表

在创建表的同时, 可以将表的内容进行填充, 使用select语句

1
2
3
4
5

create table tdb_goods_brands (
    brand_id smallint unsigned primary key auto_increment,
    brand_name varchar(40) not null
)
select brand_name from tdb_goods group by brand_name;

同样的, 我们需要在处理完成之后, 根据分类对原表进行更新

此时要注意的是, 我们在两个表中都有brand_name这个字段, 因此我们使用时, mysql并不知道我们两个字段指得是谁的, 所以一般通用的做法是, 做一个别名

1

update tdb_goods as a inner join tdb_goods_brands as b on a.brand_name = b.brand_name set a.brand_name = brand_id;

通过别名的方式, 可以是mysql对相应的字段进行操作

但是此时我们虽然改了原表中的内容, 但是现在还存在一个问题, 就是我们原表的数据中是用的varchar进行存储的, 自然会浪费比较多的空间, 此时我们可以使用alter来对表内容中的字段名称和类型进行一下更改

1
2
3

alter table tdb_goods
change goods_cate cate_id smallint unsigned not null,
change brand_name brand_id smallint unsigned not null;

连接

无限极分类表设计

1
2
3
4
5

create table tdb_goods_types(
    type_id smallint unsigned primary key auto_increment,
    type_name varchar(20) not null,
    parent_id smallint unsigned not null default 0
);

此时我们插入一些数据, parent_id对应了同表中的type_id字段

因此我们可以在这种情况下, 直接使用这个表来连接其表本身

1
2

select s.type_id,s.type_name,p.type_name from tdb_goods_types as s join tdb_goods_types as p
on s.parent_id = p.type_id;

字符函数

concat

连接两个或多个字符串

1

select concat('a', 'b', 'c'); // abc

concat_ws()

使用指定字符串连接后面的字符串

1

select concat_ws('-', 'a', 'b', 'c'); // a-b-c

format

数字格式化, 会将数字加上千分位, 并省略到小数点后某位

1

select format(123456.456, 2);  // 123,456.46

lower

转换为小写

upper

转换为大写

left

获取左侧字符

1

select lower(left('MySQL'), 2);  // my

right

获取右侧字符

length

获取字符串长度

1

select length('my sql'); // 6

ltrim

去掉前导空格

rtrim

取消后导空格

trim

去掉前后空格

除删除空格外, 还可以删除掉某些特定字符串

1

select trim(leading '?' from '???mysql????'); // mysql????

去掉左边的问号

1

select trim(trailing '?' from '???mysql????'); // ???mysql

去掉右边的问号

1

select trim(both '?' from '???mysql????');  // mysql

去掉左右两边的问号

replace

为字符串中的a字符替换为b字符

1

select replace('??mysq?l', '!'); // !!mysq!l

substring

字符串截取, 需要注意的是字符串从1开始截取, 第二个参数为截取的字符串长度

如果只传一位数时, 是从该位开始, 往后

第一位数可以传负数, 如传-1表示从倒数第一位开始

1

select substring('mysql', 1, 2); // my

查找类似值

1

select * from test where first_name like '%1%';

需要注意的是, %代表任意个字符, 但是_代表的是任意个字符, 当我们需要查找这两种字符本身的时候, 需要使用escape方法

1

select * from test where first_name like '%1%%' escape '1';

数值运算符

ceil

进一取整

1

select ceil(3.01); // 4

floor

舍一取整

div

整数除法

1

select 3 div 4; // 0

mod

取余数, 和%等价

1
2

select 3 mod 4; // 3
select 3 % 4; // 3

power

幂运算

round

四舍五入

truncate

数字截取, 不同于四舍五入, 是将该位上的内容拿掉, 传负数代表是整数位

1

select truncate(1234.321, -1); // 1230

从外部导入mysql表

导入的方式应为, 先导入schema表, 用于创建sql语句的表头结构, 然后再导入data内容

• 在MYSQL 命令下： SOURCE 下载路径/sakila-schema.sql #建立表结构

• 在MYSQL 命令下： SOURCE 下载路径/sakila-data.sql #插入数据