mysql常用命令

-- 创建数据库
CREATE DATABASE wanggong
CREATE DATABASE wanggong2
-- 查看数据库
SHOW DATABASES 

-- 删除数据库
DROP DATABASE wanggong2

-- 创建表
USER wanggong;
CREATE TABLE student(
	stuNo INT,
	stuName VARCHAR(30),
	sex CHAR(2),
	score DOUBLE,
	isMembery BOOLEAN,
	goSchoolDate DATE,
	phonNumber CHAR(11),
	age INT
);

-- 查看表
SHOW TABLES;
SELECT	* FROM student

CREATE TABLE dog(
	id INT,
	dogname VARCHAR(30),
	age INT,
	sex CHAR(2)
);

-- 删除表
DROP TABLE dog;

-- 查看表的结构
DESC student;

-- 修改表的名称
RENAME TABLE student TO stu
ALTER TABLE student RENAME TO stu

-- 新增字段
ALTER TABLE student ADD classId INT FIRST;
ALTER TABLE student ADD classId2 INT AFTER sex;

-- 删除某个字段
ALTER TABLE student DROP classId;

-- 修改字段类型，位置
ALTER TABLE student MODIFY phonNumber CHAR(11)
ALTER TABLES student MODIFY phonNumber CHAR(11) AFTER age;

-- 修改字段名称
ALTER TABLE student CHANGE score scores INT;
ALTER TABLE student CHANGE scores score DOUBLE;

-- 添加数据一（可以随便对应添加）
INSERT INTO student (stuNo ,stuName ,sex ,score,isMembery,goSchoolDate,phonNumber,age)VALUES(1,'王源','男',88.8,TRUE,'2019-10-10',159456655,10);
INSERT INTO student (stuNo ,sex ,score,isMembery,goSchoolDate,phonNumber,age)VALUES(16,'男',88.8,TRUE,'2019-10-10',159456655,10);
-- 添加方式二（全部都要写上）
-- 1.
INSERT INTO student VALUES(2,'易烊千玺','男',98,TRUE,'2019-10-10',159456655,10);
INSERT INTO student VALUES (4,'王俊','男',100,TRUE,'2019-10-10',159456655,10);
INSERT INTO student VALUES (6,'王凯','男',10,TRUE,'2019-10-10',159456655,10);
INSERT INTO student VALUES (7,'王俊凯','男',15,TRUE,'2019-10-10',159456655,10);
INSERT INTO student VALUES (8,'凯','男',20,TRUE,'2019-10-10',159456655,10);
INSERT INTO student VALUES (9,'王','男',30,TRUE,'2019-10-10',159456655,10);
INSERT INTO student VALUES (10,'凯凯','男',40,TRUE,'2019-10-10',159456655,10);
INSERT INTO student VALUES (11,'俊','男',50,TRUE,'2019-10-10',159456655,10);
INSERT INTO student VALUES (11,'王_','男',50,TRUE,'2019-10-10',159456655,10);
INSERT INTO student VALUES (15,,'男',50,TRUE,'2019-10-10',159456655,10);
-- 2.
INSERT INTO student VALUES (12,'阿三','男',14,TRUE,'2019-10-10',159456655,10),
(13,'萨芬','男',19,TRUE,'2019-10-10',159456655,10), 
(14,'王分','男',100,TRUE,'2019-10-10',159456655,10);

-- 查询
SELECT * FROM student WHERE stuName LIKE '王%'
SELECT * FROM student WHERE stuName LIKE '王_'
SELECT * FROM student WHERE stuName LIKE '王\_'

SELECT * FROM student 
-- 删除
DELETE FROM student WHERE stuName=''

-- 算术运算符
SELECT score+20,stuName FROM student 
-- 关系 
SELECT * FROM student WHERE score>80 AND score<90
-- 逻辑运算符
SELECT * FROM student WHERE score BETWEEN 80 AND 90
SELECT * FROM student WHERE score>80 OR stuNo<4

-- is 关键字
SELECT * FROM student WHERE stuName IS NULL
SELECT * FROM student WHERE stuName IS  NOT NULL