创建一个库 create database xiaojiejie DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
查看库 show databases;
进入我们创建的库 use xiaojiejie;
查看我们有没有进入到创建的库 select database();
创建一个表
create table yu1(
id int auto_increment primary key,
name varchar(12) not null,
age int not null,
phone char(11)
);
ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; 可以写入汉字
在表中写入你想要的数据 注意格式 insert into yu2(id,name,age,phone) values (1,'犇犇',21,13838385438);
select * from 表名； 查看表中所写内容
show tables; 查看库中有什么表
desc 表名 describe 表名 show columns from 表名 第一个是第二个的缩写，第三个是查看多个列表，它们都可以查到表结构。
insert into 表 (列名,列名.......) values (值,值.........) 往表内插入一条数据
insert into 表 (列名,列名.......) values (值,值.........),(值,值......) 往表内插入多条数据
drop database 库名 删库
drop taple 表名 删表
truncate table 表名 表在删内容
select * from mysql.user\G
show create database 库名\G 查有关的数据
清空表数据
TRUNCATE [TABLE] 表名
-- 复制表结构
CREATE TABLE 表名 LIKE 要复制的表名
-- 复制表结构和数据
CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名
-- 检查表是否有错误
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
-- 优化表
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
-- 修复表
REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]
-- 分析表
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]
insert into 表1 (列名,列名...) select (列名,列名...) from 表2:列名不需要一致，但是列的属性需要一致 delete from 表名 where name='ll' order by id desc（倒序）limit 1;
select * from 表名 where name='ll' order by id desc limit 1;
ALTER TABLE liule ADD FOREIGN KEY(student_id) REFERENCES student (id);
添加外键 select age,id from 表 where name='ll';
select age as 年龄,from 表 where name='jkl';
select * from 表 where id between 3 and 7;
select * from 表 where id in (3,5,7);
select * from 表 where id not in (3,5,7);
select * from 表 where id in (3,5,7);
select * from 表 where id in (select id from 表)
通配符
select * from 表 where name like 'sha%' - sha开头的所有（多个字符串）
select * from 表 where name like 'shar_' - sha开头的所有（一个字符）
select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序
select count(*) from student group by class_id;
select count(*) sum(age),max(age) from student group by class_id;
select age,count(*) from 表 group by age having count(*) > 10;
连表查询：
select s.name as '学生姓名',c.name as '班级名',t.name as '老师姓名' from student as s inner join class as c join teacher as t on s.class_id=c.id and s.teacher_id=t.id;
select s.name as '学生姓名',c.name as '班级名',t.name as '老师姓名' from student as s,classs as c,teacher as t where s.class_id=c.id and s.teacher_id=t.id;
select server.ip, server.id as '服务器id',jifang.address from server,jifang where jifang.id=server.jifang_id and jifang.address='亦庄' and server.ip like '192.168%';
