mysql 速记

mysql
500

查询数据库

  • SHOW DATABASES;

创建数据库

  • CREATE DATABASE IF NOT EXISTS dbanme;

删除数据库

  • DROP DATABASE IF EXISTS dbname;

使用数据库

  • USE dbname;
  • SELECT DATABASE(); #查询当前使用的数据库

查询当前数据库下的表

  • SHOW TABLES;

查询表的结构

  • desc tbname;

创建表

 CREATE TABLE tbname(
    uid int,
    name varchar(10),
    gender char,
    score double(5,2),
    createdate date,
    email varchar(64)
);

约束

  • 非空约束 NOT NULL
  • 唯一约束 UNIQUE
  • 主键约束 PRIMARY KEY

主键自增 PRIMARY KEY AUTO_INCRIMENT

  • 检查约束 CHECK
  • 默认约束 DEFAULT
  • 外键约束 FROEIGN KEY

CONSTRAINT fk_tb1_tb2 FOREIGN KEY(id) REFEREN tb2(id)

删除数据表

  • DROP TABLE IF EXISTS tbname;

操作表结构

修改表名

  • alter TABLE old_tbname rename to new_tbname

添加添加一列

  • alter table tbname add list_name list_type

修改一列的数据类型

  • alter table tbname modify list_name new_list_type;

修改列名和数据类型

  • alter table tbname change list_name new_list_name new_list_type;

删除一列

  • alter table tbname drop list_name;

数据的增删改查

查询所有数据

  • select * from taname;

添加数据

  • insert into tbname(list_1,list_2, ...) values (list_value1,list_value2, ...);

  • insert into tbname values (list_value1,list_value2, ...);

  • insert into tbname values (list_value1,list_value2, ...),(list_value1,list_value2, ...),(list_value1,list_value2, ...),...;

修改数据

  • update tbname set list_name1 = list_value1,list_name2 = list_value2 WHERE list_name = value;

删除数据

  • delete from tbname WHERE list_name = value;

查询数据

*** select … from … where … group by … having … order by … limit … ***

# 测试用例

drop TABLE if EXISTS user;


CREATE table user(
   id int,
   name VARCHAR(10),
   age INT,
   gender CHAR(1),
   address VARCHAR(50),
   math double(5,2),
   english DOUBLE(5,2),
   hire_date date
);


INSERT INTO user VALUES
(1,'张珊',23,'女','重庆',25,22,'2001-05-18'),
(2,'李思',13,'男','北京',24,52,'2005-06-28'),
(3,'吴楠',53,'女','天津',45,78,'2003-07-23'),
(4,'张工',55,'男','南京',25,72,'2003-06-17'),
(5,'洪雅',30,'男','湖北',77,62,'2008-10-29'),
(6,'小乐',23,'男','张家口',99,99,'2005-11-26'),
(7,'小桥',56,'男','海南',67,56,'2002-01-05'),
(8,'李加',23,'女','三亚',59,23,'2003-02-20'),
(9,'岑里拉',23,'女','贵州',80,45,'2005-08-25'),
(10,'成龙',5,'男','昆明',90,22,'2002-07-24'),
(11,'小红',12,'男','重庆',95,60,'2003-03-14'),
(12,'小张',23,'女','山西',65,22,'2002-9-6'),
(13,'孔亚',13,'男','武汉',45,60,'2006-12-30'),
(14,'张春',24,'女','浙江',29,100,'2004-8-25'),
(15,'程鸡',16,'男','昆明',20,12,'2005-03-04'),
(16,'余四',22,'女','湖南',21,2,'2002-06-09');


select * from user;


select DISTINCT address from user;

select name as 姓名,math as 数学成绩,english as 英语成绩 from user;

查询所有

  • select * from tbname;

查询去重

  • select DISTINCT address from tbname;

查询别名 as

  • select name as 姓名, math as 数学成绩, english as 英语成绩 from tbname;

条件查询 where

条件

  •   <   >   =   <>   !=

  •   or   and   not

  •   between … and …

  •   in(…)

  •   like   # 模糊查询 占位符: _单个任意字符 %多个任意字符

  •   is null    is not null

  • SELECT name as 姓名,math as 数学 from user where math BETWEEN 23 and 60 ORDER BY math;

分页查询 limit

  • SELECT * FROM user LIMIT 3,3;

聚合函数

函数

  •   count(*);

  •   max(列名);

  •   min(列名);

  •   sum(列名)；

  •   avg(列名);

  • select 聚合函数 from tbname;

分组查询

  • SELECT gender,AVG(math) as mathavg from user GROUP BY gender ORDER BY mathavg DESC;

多表查询

内连接

  • 显示

select * from tbname1 join tbname2 on [条件] ;

  • 隐式

select * from tbname1,tbname2 where [条件] ;

外连接

  • 左外连接

select * from tbname1 left join tbname2 on [条件] ;

  • 右外连接

select * from tbname1 right join tbname2 on [条件] ;

子查询 (sql)

事务

  • 开启事务

START TRANSACTION; 或者 BEGIN;

  • 提交事务

COMMIT;

  • 回滚事务

ROLLBACK;