#登录和退出数据库mysql -u root -p #输入密码 -P指定端口,-D指定数据库 ,--prompt 修改提示符exit #quit,\qmysql -u root -p
SELECT VERSION();
SELECT NOW();
SHOW ENGINES;
SHOW VARIABLES LIKE 'have%'; #查看存储引擎信息SHOW VARIABLES LIKE 'storage_engine'; #查看默认存储引擎SHOW WARNINGS \G;
#数据库创建,删除,进入数据库SHOW DATABASES;
DROP DATABASE IF EXISTS `testdatabase`;
CREATE DATABASE IF NOT EXISTS `testdatabase` DEFAULT CHARACTER SET = 'utf8';
ALTER DATABASE testdatabase CHARACTER SET = 'utf8';
SHOW CREATE DATABASE `testdatabase`;
USE testdatabase; #这里不应该使用 `XX` 括住名称SELECT USER();
SELECT DATABASE();
SHOW TABLES; #注释符号help 'SELECT';
help 'INT';
help '+';
#表的创建和删除,创建表同时设置属性CREATE TABLE IF NOT EXISTS testestable(
id INT ZEROFILL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) UNIQUE, #检索效率 CHAR>VARCHAR>TEXT age TINYINT UNSIGNED NOT NULL,
brithday DATE, #1000-01-01~9999-12-31 3个字节 sex TINYINT(1) DEFAULT 0 COMMENT '0代表男,1代表女',
address TINYTEXT,
married ENUM('是','否','保密'), #最多65535个值 fav_food SET('apple','orange','fish','meat','cake') DEFAULT NULL, #最多64个成员 score DECIMAL(2,2),
CHECK(score > 0)
) ENGINE = 'InnoDB' CHARACTER SET = 'utf8';
# InnoDB(支持事务、崩溃修复、并发控制,适合频繁更新删除)# MyISAM(适合对数据的存储和读取,空间使用比较低)、# MEMORY(速度快、安全性差)SHOW CREATE TABLE testestable \G;
DESC testestable;
SHOW FULL COLUMNS FROM testestable \G;
SHOW TABLES;
DROP TABLE testestable;
#key键: #主键:唯一标识一条记录,不能有重复的,不允许为空,用来保证数据完整性CREATE TABLE IF NOT EXISTS test1(
username CHAR(20),
password CHAR(20),
PRIMARY KEY(username)
);
#外键:外键是另一表的主键, 一个表可以有多个外键, 可以是空值,用来和其他表建立联系用的 #外键连接必须连接的是主表的主键,如果连接主表主键的一部分,必须连接主键的第一个字段 #外键关联的主从表字段数据类型必须完全一样CREATE TABLE IF NOT EXISTS test2(
id INT PRIMARY KEY AUTO_INCREMENT,
username CHAR(20),
level TINYINT UNSIGNED
#CONSTRAINT uname FOREIGN KEY(username) REFERENCES test1(username));
#修改表: #修改表名ALTER TABLE test1 RENAME temp;
SHOW TABLES;
DESC temp;
ALTER TABLE temp RENAME TO test1;
#修改表属性数据类型DESC test1;
ALTER TABLE test1 MODIFY password VARCHAR(50);
DESC test1;
INSERT test1 VALUES('FreezeJ','123456');
SELECT * FROM test1;
#ALTER TABLE test1 MODIFY username int; #有值的情况下转换字段类型失败,因为字符串类型无法自动转型为整数类型SHOW WARNINGS LIMIT 1; #查看第一条错误DELETE FROM test1; #删除表中所有元素SELECT * FROM test1;
ALTER TABLE test1 MODIFY username int;
DESC test1;
ALTER TABLE test1 MODIFY username CHAR(10);
#修改表属性ALTER TABLE test1 CHANGE password passwd CHAR(20);
#增加字段DESC test1;
ALTER TABLE test1 ADD sex TINYINT(1) NOT NULL after passwd;
#删除字段ALTER TABLE test1 DROP sex; #删除字段会直接删除,不管有没有内容,也不会要求确认
#修改属性排列顺序ALTER TABLE test1 MODIFY username CHAR(10) AFTER passwd; #会修改username的类型ALTER TABLE test1 MODIFY username CHAR(10) FIRST;
#索引: #创建带索引的数据库表CREATE TABLE IF NOT EXISTS test3(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10) NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
UNIQUE INDEX index_id(id)
); #唯一索引,唯一索引指的是被索引的字段值唯一SHOW CREATE TABLE test3 \G;
#在已存在表上添加索引#create [unique|fulltext|spatial] index indexname on tablename (columnname [(length)] [ASC|DESC]); #Alter语句添加索引 #Alter table tablename add [unique|fulltext|spatial] index indexname (columename [(length)] [ASC|DESC]);CREATE INDEX index_username ON test1(username);
ALTER TABLE test1 ADD INDEX index_passwd(passwd);
#查看索引SHOW INDEX FROM test1 \G;
#删除索引DROP INDEX index_username ON test1;
ALTER TABLE test1 DROP INDEX index_passwd;
CREATE TABLE student(
id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name CHAR(20) NOT NULL,
num INTEGER UNSIGNED UNIQUE NOT NULL,
age TINYINT UNSIGNED NOT NULL,
sex TINYINT(1) DEFAULT 0,
address CHAR(50),
room CHAR(5),
class TINYINT
);
CREATE TABLE teacher(
id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name CHAR(20) NOT NULL,
sex TINYINT(1) DEFAULT 1 COMMENT '1代表女',
teach_class TINYINT DEFAULT NULL
);
#插入INSERT INTO student VALUES(1,'DRJ', 11, 21, 0, 'AAAA', '6-208', 1);
INSERT INTO student VALUES(2,'LYC', 10, 20, 0, 'AAAA', '6-210', 2);
INSERT INTO student VALUES(3,'LZY', 25, 23, 0, 'BBBB', '6-208', 3);
INSERT INTO student VALUES(4,'WJX', 5, 21, 0, 'CCCC', '6-207', 1);
INSERT INTO student VALUES(5,'YJH', 29, 21, 0, 'DDDD', '6-208', 1);
INSERT INTO student VALUES(6,'WL', 33, 21, 0, 'CCCC', '6-208', 5);
INSERT INTO teacher VALUES(1,'YYY', 1, 2);
INSERT INTO teacher VALUES(2,'QQQ', 2, 3);
INSERT INTO teacher VALUES(3,'WWW', 1, 1);
INSERT INTO teacher VALUES(4,'EEE', 2, 3);
INSERT INTO teacher VALUES(5,'RRR', 1, 4);
#更改属性ALTER TABLE student MODIFY sex TINYINT(1) COMMENT '0代表男';
#更新属性UPDATE student SET name = 'ZXB', num = 27 WHERE id = 4;
#删除属性DELETE FROM student WHERE name = 'WL';
INSERT INTO student VALUES(6,'WL', 33, 21, 0, 'CCCC', '6-208', 5);
#常用查询语句
SELECT name, address FROM student WHERE room LIKE '__208' ORDER BY age DESC;
SELECT name, room FROM student GROUP BY room;
SELECT name FROM student WHERE age = (SELECT age FROM student ORDER BY age DESC LIMIT 1);
SELECT name AS '宿舍成员' FROM student GROUP BY room;
SELECT DISTINCT age AS '年龄分布' FROM student ORDER BY age;
SELECT student.name AS '学生名字',student.class AS '班级', teacher.name AS '老师姓名'
FROM student JOIN teacher
ON student.class = teacher.teach_class;
SELECT name FROM student
UNION #UNION自动去重复,UNION ALL可以不去重复SELECT name FROM teacher;
#聚集函数#AVG() - 返回平均值#COUNT() - 返回行数#FIRST() - 返回第一个记录的值#LAST() - 返回最后一个记录的值#MAX() - 返回最大值#MIN() - 返回最小值#SUM() - 返回总和SELECT count(*) AS '总人数' FROM student;
CREATE VIEW view_room AS
SELECT name, room
FROM student
WHERE room = '6-208';
SHOW TABLES;
SELECT * FROM view_room;
INSERT INTO student VALUES (7,'WJZ', 17, 21, 0, 'GGGG', '6-208', 5);
SELECT * FROM view_room;