MYSQL 基础语句

查看数据库

SHOW DATABASES;
SHOW DATABASES LIKE ‘%edu%’;

创建数据库

CREATE DATABASES test_db1;
CREATE DATABASES IF NOT EXISTS test_db2;
CREATE DATABASES test_db3;
CREATE DATABASES IF NOT EXISTS test_db4 DEFAULT CHARACTER SET uft8 DEFAULT COLLATE utf8_chinese_ci;

修改数据库,没有直接修改数据库名字的方法

ALTER DATABASES test_db4 DEFAULT CHARACTER SET gb2312 DEFAULT COLLATE gb2312_chinese_ci;

删除数据库

DROP DATABASE test_db1;
DROP DATABASE IF EXISTS test_db1;

选择数据库

USE test;

MySql注释

– 注释
/注释/

MYSQL HELP

help contents

创建数据表

CREATE TABLE tb_tmp1
     (
     id INT(11),
     name VARCHAR(25),
     deptId INT(11),
     salary FLOAT
     );

查看表结构

DESC tb_tmp1;

SHOW CREATE TABLE tb_tmp1\G;

修改表名

ALTER TABLE tb_tmp1 RENAME TO tb_student;

修改表字符集

ALTER TABLE tb_student CHARACTER SET gb2312 DEFAULT COLLATE gb2312_chinese_ci;

修改表字段

ALTER TABLE tb_student MODIFY name VARCHAR(30);

删除表字段

ALTER TABLE tb_student DROP name;

添加表字段,在开头，默认在结尾

ALTER TABLE tb_student ADD age INT(4) first;

删除数据表

DROP TABLE tb_student;

创建表时设置主键约束

CREATE TABLE tb_tmp1
     (
     id INT(11) PRIMARY KEY,
     name VARCHAR(25),
     deptId INT(11),
     salary FLOAT
     );

创建表时设置主键约束

CREATE TABLE tb_tmp1
     (
     id INT(11),
     name VARCHAR(25),
     deptId INT(11),
     salary FLOAT,
     PRIMARY KEY(id)
     );

#创建表时设置联合主键约束

CREATE TABLE tb_tmp1
     (
     id INT(11),
     name VARCHAR(25),
     deptId INT(11),
     salary FLOAT,
     PRIMARY KEY(id,deptId)
     );

修改表时添加主键

ALTER TABLE tb_emp2 ADD PRIMARY KEY (id);

主键自增长

CREATE TABLE tb_tmp1
     (
     id INT(11) AUTO_INCREMENT,
     name VARCHAR(25),
     deptId INT(11),
     salary FLOAT,
     PRIMARY KEY(id,deptId)
     );

外键约束

非空约束

CREATE TABLE tb_tmp1
     (
     id INT(11) AUTO_INCREMENT PRIMARY KEY ,
     name VARCHAR(25) NOT NULL,
     deptId INT(11),
     salary FLOAT,
     FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
     );

唯一约束

检查约束

默认值

CREATE TABLE tb_tmp1
     (
     id INT(11) AUTO_INCREMENT PRIMARY KEY ,
     name VARCHAR(25) UNIQUE,
     deptId INT(11) UNIQUE,
     salary FLOAT CHECK(salary>0 AND salary<100),
     location VARCHAR(50) DEFAULT 'Beijing',
     FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
     );

去重查询

SELECT DISTINCT name FROM tb_tmp1;

设置别名

SELECT name as student_name FROM tb_tmp1

SELECT stu.name FROM tb_tmp1 AS stu;

限制查询结果条数

SELECT * FROM tb_tmp1 LIMIT 3,5;

限制查询结果条数,不指定初始位置

SELECT * FROM tb_tmp1 LIMIT 5;

对查询结果排序

SELECT * FROM tb_tmp1 ORDER BY height,name;

条件查询

SELECT * FROM tb_student WHERE height=170;
SELECT * FROM tb_student WHERE age<22 AND height>175;
SELECT * FROM tb_student WHERE age<22 OR height>175;
SELECT * FROM tb_student WHERE age IS NULL;
SELECT * FROM tb_student WHERE age IS NOT NULL;
SELECT * FROM tb_student WHERE age BETWEEN 10 AND 15;

查询姓张的

SELECT * FROM tb_student WHERE name LIKE '张%';

查询姓名带华的

SELECT * FROM tb_student WHERE name LIKE '%华%';

查询三个字的

SELECT * FROM tb_student WHERE name LIKE '___';

分组查询,筛选男女生

SELECT 'sex' GROUP_CONTACT(name) FROM tb_student GROUP BY sex;

SELECT sex,COUNT(sex) FROM tb_student GROUP BY sex;

HAVING 根据前面已经查询出的字段进行过滤，HAVING 会报错

SELECT name FROM tb_student WHERE height=170;
SELECT name FROM tb_student HAVING height=170;

交叉连接

SELECT * FROM course CROSS JOIN student;

内连接

SELECT s.name,c.course_name FROM student s INNER JOIN course c ON c.course_id = c.id;

左外连接，查询包括没有课程的学生

SELECT s.name,c.course_name FROM student s LEFT OUTER course c ON c.course_id = c.id;

右外连接，查询包括没有学生的课程

SELECT s.name,c.course_name FROM student s RIGHT OUTER course c ON c.course_id = c.id;

子查询

SELECT * FROM student where course_id IN (SELECT id FROM course WHERE course_name = 'Java');

正则表达式查询，用到了去翻

视图、索引、存储过程和触发器