-- 创建数据库
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
mysql常用命令
最新推荐文章于 2024-06-05 22:54:12 发布