MySQL 常用基础知识，多学一门技能，不求人

CREATE DATABASE 数据库名;

show databases;

use 数据库名;

show tables;

drop database 数据库名;

CREATE DATABASE 数据库名(firstname VARCHAR(20), lastname VARCHAR(35));

describe 表名;
DESC 表名；

ALTER table 表名 RENAME TO 表名2;

drop TABLE 表名；

ALTER TABLE 表名 ADD 字段名 VARCHAR(40);

ALTER TABLE 表名 ADD 字段名 VARCHAR(40) AFTER 字段名1;
desc 表名;

ALTER TABLE 表名 DROP 字段名;

INSERT INTO '表名' ('字段1', '字段2') VALUES ("内容1", "内容2");

SELECT * FROM 表名;

SELECT 字段1, 字段2 FROM 表明;

SELECT * FROM 表名 ORDER BY 字段名 DESC;

SELECT * FROM 表名 WHERE 字段=112;

SELECT * FROM 表名 WHERE 字段=112 or 字段=148;
SELECT * FROM 表名 WHERE country="USA" AND city="New York";
SELECT * FROM 表名 WHERE 字段 BETWEEN 1000 and 2000;
SELECT * FROM 表名 WHERE 字段 in (114, 151, 259);
### 查询包含Gift的字段
SELECT * FROM 表名 WHERE 字段 LIKE "%Gift%";
### 查询以Gift开头的
SELECT * FROM 表名 WHERE 字段 LIKE "Gift%";
### 查询以Gift结尾的
SELECT * FROM 表名 WHERE 字段 LIKE "%Gift";

SELECT 字段名1, 字段名2 FROM 表名 重命名表 INNER JOIN 表名2 重命名表2 on 重命名表.'name'=重命名表2.'name';

SELECT 重命名表.字段名1, 重命名表.字段名2 重命名表2.字段名3 重命名表2.字段名4 FROM 表名 重命名表 left JOIN 表名2 重命名表2 on 重命名表.'name'=重命名表2.'name' where 重命名表2.字段名4 is NULL;

SELECT 重命名表.字段名1, 重命名表.字段名2 重命名表2.字段名3 重命名表2.字段名4 FROM 表名 重命名表 right JOIN 表名2 重命名表2 on 重命名表.'name'=重命名表2.'name';

SELECT 字段 FROM 表1 CROSS JOIN 表2;

SELECT count(*) FROM 表名 WHERE conditions

SELECT 
字段1, sum(case when 字段2=具体数值/内容 then 1 else 0 end) as 0, sum(case when 字段2=具体数值/内容2 then 1 else 0 end) as 1 
FROM 表名
GROUP BY 字段1

CREATE DATABASE mydata;

USE mydata;

DROP DATABASE mydata;

CREATE TABLE student(                                   nt(

id INT,### 整型

studentName VARCHAR(20),### 可变长字符类型

grade DECIMAL(10,2)### 有效长度为10，保留两位小数

)DEFAULT CHARSET=utf8;### 设置编码集为utf8

INSERT INTO student(id,studentName,grade) VALUES (32,"张三",70);

SELECT *FROM student;

SELECT id,studentName,grade FROM student;

DROP TABLE student;

UPDATE student SET studentName="李四" WHERE id=32;

DELETE  FROM student WHERE id=32;

CREATE TABLE mystu SELECT * FROM student;

CREATE TABLE mystu1 SELECT * FROM student WHERE 1=0;

ALTER TABLE student ADD sex VARCHAR(10);

ALTER TABLE student CHANGE sex birthday VARCHAR(30);

ALTER TABLE student DROP COLUMN birthday;

CREATE TABLE person(

### 设置id列为主键列:不能为null,同时不能重复,AUTO_INCREMENT表示设置主键列为自动增长列

id INT PRIMARY KEY AUTO_INCREMENT,

NAME VARCHAR(40) UNIQUE, ### 姓名列不能重复

sex VARCHAR(40) NOT NULL ### 增加非空约束

)AUTO_INCREMENT=1000;### 列号起始为1000

INSERT INTO person(NAME,sex) VALUES("张三","男");

INSERT INTO person(NAME,sex) VALUES("李四","男");

INSERT INTO person(NAME,sex) VALUES("王五","男");

ALTER TABLE person ADD contry VARCHAR(40)DEFAULT "中国";

CREATE TABLE computer(

id INT PRIMARY KEY AUTO_INCREMENT,

computerType VARCHAR(30),

personid INT ,

CONSTRAINT fk_com FOREIGN KEY(personid) REFERENCES person(id)

)ENGINE=INNODB;

ALTER TABLE computer ADD CONSTRAINT fk_com FOREIGN KEY(personid) REFERENCES person(id);

START TRANSACTION;

DELETE FROM person;

COMMIT;

ROLLBACK;

TRUNCATE TABLE dog;

UPDATE person SET NAME=CONCAT(NAME,"你好");

SELECT p.NAME 姓名 FROM person p;

INSERT INTO product(productName,price,number,factory)VALUES("电视机",1500,2,"绵阳");

INSERT INTO product(productName,price,number,factory)VALUES("电冰箱",2500,1,NULL);

INSERT INTO product(productName,price,number,factory)VALUES("毛毯",150,5,"内蒙古");

INSERT INTO product(productName,price,number,factory)VALUES("苹果",5,6,NULL);

INSERT INTO product(productName,price,number,factory)VALUES("板凳",150,10,"成都");

INSERT INTO product(productName,price,number,factory)VALUES("芒果",10,8,"海南");

SELECT p.*,price*number totalprice FROM product p

SELECT DISTINCT price FROM product;

SELECT * FROM product LIMIT 1,3

SELECT * FROM product WHERE price>1000 AND factory="绵阳"

SELECT * FROM product WHERE price>1000 OR factory="绵阳"

SELECT * FROM product WHERE price BETWEEN 100 AND 2000

SELECT * FROM product WHERE id IN(3,5);

SELECT * FROM product WHERE id NOT IN(3,5);

SELECT * FROM product WHERE productName LIKE "%电%";

### 查询厂家为null 的商品
```sql
SELECT * FROM product WHERE factory IS NULL

SELECT * FROM product ORDER BY price,id;### 升序

SELECT * FROM product ORDER BY price DESC,id DESC;### 降序