查看某个库有多少表,先进入库
show tables;
查看某个新建表信息
show create table 表名;
查看表结构
desc 表名;
describe 库名.表名;
查看表的所有字段内容
select * from 表名;
mysql> select * from host;
Empty set (0.00 sec) #表中没有内容,空集
查看表的某些字段的内容
select id from host; #查看host表的id字段的内容
select id,port from host; #查看host表的id字段和port字典的内容
查看表的某一行的内容
select * from user wherer user_name='zhangsan' #查看user表中user_name等于zhangsan的这一行的所有字段
#查看字符集是否支持中文
show variables like 'character_set_%';
示例:
mysql> show tables;
+----------------+
| Tables_in_xixi |
+----------------+
| host |
+----------------+
1 row in set (0.00 sec)
mysql> show create table host; ±------±------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | ±------±------------------------------------------------------------------------------------------------------------------------------------------------------------+ | host | CREATE TABLE host ( id int(10) DEFAULT NULL, hostname char(20) DEFAULT NULL, port int(5) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | ±------±------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> desc host; ±---------±---------±-----±----±--------±------+ | Field | Type | Null | Key | Default | Extra | ±---------±---------±-----±----±--------±------+ | id | int(10) | YES | | NULL | | | hostname | char(20) | YES | | NULL | | | port | int(5) | YES | | NULL | | ±---------±---------±-----±----±--------±------+ 3 rows in set (0.01 sec) field 代表字段名 type 代表该字段类型, Null 该字段是否可以为空 default 该字段的默认设置 extra 额外的设置
mysql> select * from xixi.host; Empty set (0.00 sec) #empty这里表示一张空表
mysql> select id,port from xixi.host; Empty set (0.00 sec)
mysql> select * from user where user_name=‘zhangsan’;
+-----------+-------------------------------------------+ | user_name | user_passwd | +-----------+-------------------------------------------+ | zhangsan | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-----------+-------------------------------------------+ 1 row in set (0.00 sec)
create table employee(
id int primary key auto_increment ,
name varchar(20),
gender bit default 1, -- gender char(1) default 1 ----- 或者 TINYINT(1)
birthday date,
entry_date date,
job varchar(20),
salary double(4,2) unsigned,
resume text -- 注意,这里作为最后一个字段不加逗号
);
mysql> REVOKE all ON crushlinux.* FROM 'teacher'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW GRANTS FOR 'teacher'@'localhost';
+---------------------------------------------+
| Grants for teacher@localhost |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'teacher'@'localhost' |
+---------------------------------------------+
1 row in set (0.01 sec)
显示服务器信息
mysql> status; -------------- mysql Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using EditLine wrapper
Connection id: 29 Current database: client Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.24-log Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 2 days 42 sec
Threads: 1 Questions: 276 Slow queries: 0 Opens: 125 Flush tables: 1 Open tables: 112 Queries per second avg: 0.001 --------------
mysql> grant all on *.* to 'root'@'192.168.200.2' IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
Windows客户机安装Navicat客户端工具
修改表结构(字段)
修改一列类型
alter table tab_name modify 列名 类型 [完整性约束条件][first|after 字段名];
alter table users2 modify age tinyint default 20;
alter table users2 modify age int after id;
修改列名 alter table tab_name change [column] 列名 新列名 类型 [完整性约束条件][first|after 字段名]; alter table users2 change age Age int default 28 first;
删除一列 alter table tab_name drop [column] 列名; – 思考:删除多列呢?删一个填一个呢? alter table users2 add salary float(6,2) unsigned not null after name, drop addr;
修改表名 rename table 表名 to 新表名; 修该表所用的字符集 alter table student character set utf8;
删除表 drop table tab_name;
添加主键,删除主键 alter table tab_name add primary key(字段名称,…) alter table users drop primary key;
eg: mysql> create table test5(num int auto_increment); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key create table test(num int primary key auto_increment); – 思考,如何删除主键? alter table test modify id int; – auto_increment没了,但这样写主键依然存在,所以还要加上下面这句 alter table test drop primary key;-- 仅仅用这句也无法直接删除主键
– 唯一索引 alter table tab_name add unique [index|key] 索引名称
alter table users add unique(name)-- 索引值默认为字段名show create table users;
alter table users add unique key user_name(name);-- 索引值为user_name
-- 添加联合索引
alter table users add unique index name_age(name,age);#show create table users;
-- 删除唯一索引
alter table tab_name drop {index|key} index_name</code></pre>
表记录增,删,改
-- 1.增加一条记录insert
/*insert [into] tab_name (field1,filed2,.......) values (value1,value2,.......);*/
create table employee_new(
id int primary key auto_increment,
name varchar(20) not null unique,
birthday varchar(20),
salary float(7,2)
);
insert into employee_new (id,name,birthday,salary) values
(1,'yuan','1990-09-09',9000);
insert into employee_new values
(2,'alex','1989-08-08',3000);
insert into employee_new (name,salary) values
('xialv',1000);
-- 插入多条数据
insert into employee_new values
(4,'alvin1','1993-04-20',3000),
(5,'alvin2','1995-05-12',5000);
-- set插入: insert [into] tab_name set 字段名=值
insert into employee_new set id=12,name="alvin3";
– 2.修改表记录 update tab_name set field1=value1,field2=value2,…[where 语句]
/* UPDATE语法可以用新值更新原有表行中的各列。
SET子句指示要修改哪些列和要给予哪些值。
WHERE子句指定应更新哪些行。如没有WHERE子句,则更新所有的行。*/
update employee_new set birthday="1989-10-24" WHERE id=1;
--- 将yuan的薪水在原有基础上增加1000元。
update employee_new set salary=salary+4000 where name='yuan';
– 3.删除表纪录
delete from tab_name [where ....]
/* 如果不跟where语句则删除整张表中的数据
delete只能用来删除一行记录
delete语句只能删除表中的内容,不能删除表本身,想要删除表,用drop
TRUNCATE TABLE也可以删除表中的所有数据,词语句首先摧毁表,再新建表。此种方式删除的数据不能在
事务中恢复。*/
-- 删除表中名称为’alex’的记录。
delete from employee_new where name='alex';
-- 删除表中所有记录。
delete from employee_new;-- 注意auto_increment没有被重置:alter table employee auto_increment=1;
-- 使用truncate删除表中记录。
truncate table emp_new;</code></pre>
表记录 查
CREATE TABLE ExamResult(
id INT PRIMARY KEY auto_increment, name VARCHAR (20), JS DOUBLE , Django DOUBLE , Database DOUBLE );
INSERT INTO ExamResult VALUES (1,“yuan”,98,98,98), (2,“xialv”,35,98,67), (3,“alex”,59,59,62), (4,“wusir”,88,89,82), (5,“alvin”,88,98,67), (6,“yuan”,86,100,55);
– (1)select [distinct] *|field1,field2,… from tab_name – 其中from指定从哪张表筛选,*表示查找所有列,也可以指定一个列 – 表明确指定要查找的列,distinct用来剔除重复行。
-- 查询表中所有学生的信息。
select * from ExamResult;
-- 查询表中所有学生的姓名和对应的英语成绩。
select name,JS from ExamResult;
-- 过滤表中重复数据。
select distinct JS ,name from ExamResult;
– (2)select 也可以使用表达式,并且可以使用: 字段 as 别名或者:字段 别名
-- 在所有学生分数上加10分特长分显示。
select name,JS+10,Django+10,Database+10 from ExamResult;
-- 统计每个学生的总分。
select name,JS+Django+Database from ExamResult;
-- 使用别名表示学生总分。
select name as 姓名,JS+Django+Database as 总成绩 from ExamResult;
select name,JS+Django+Database 总成绩 from ExamResult;
select name JS from ExamResult; //what will happen?---->记得加逗号
– (3)使用where子句,进行过滤查询。
-- 查询姓名为XXX的学生成绩
select * from ExamResult where name='yuan';
-- 查询英语成绩大于90分的同学
select id,name,JS from ExamResult where JS>90;
-- 查询总分大于200分的所有同学
select name,JS+Django+Database as 总成绩 from
ExamResult where JS+Django+Database>200 ;
-- where字句中可以使用:
-- 比较运算符:
> < >= <= <> !=
between 80 and 100 值在10到20之间
in(80,90,100) 值是10或20或30
like 'yuan%'
/*
pattern可以是%或者_,
如果是%则表示任意多字符,此例如唐僧,唐国强
如果是_则表示一个字符唐_,只有唐僧符合。
*/
-- 逻辑运算符
在多个条件直接可以使用逻辑运算符 and or not
-- 练习
-- 查询JS分数在 70-100之间的同学。
select name ,JS from ExamResult where JS between 80 and 100;
-- 查询Django分数为75,76,77的同学。
select name ,Django from ExamResult where Django in (75,98,77);
-- 查询所有姓王的学生成绩。
select * from ExamResult where name like '王%';
-- 查询JS分>90,Django分>90的同学。
select id,name from ExamResult where JS>90 and Django >90;
-- 查找缺考数学的学生的姓名
select name from ExamResult where Database is null;
– (4)Order by 指定排序的列,排序的列即可是表中的列名,也可以是select 语句后指定的别名。
-- select *|field1,field2... from tab_name order by field [Asc|Desc]
-- Asc 升序、Desc 降序,其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾。
-- 练习:
-- 对JS成绩排序后输出。
select * from ExamResult order by JS;
-- 对总分排序按从高到低的顺序输出
select name ,(ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0))
总成绩 from ExamResult order by 总成绩 desc;
-- 对姓李的学生成绩排序输出
select name ,(ifnull(JS,0)+ifnull(Django,0)+ifnull(Dababase,0))
总成绩 from ExamResult where name like 'a%'
order by 总成绩 desc;
– (5)group by 分组查询:
-- 注意,按分组条件分组后每一组只会显示第一条记录
-- group by字句,其后可以接多个列名,也可以跟having子句,对group by 的结果进行筛选。
-- 按位置字段筛选
select * from ExamResult group by 2;
-- 练习:对成绩表按名字分组后,显示每一类名字的JS的分数总和
select NAME ,SUM(JS)from ExamResult group by name;
-- 练习:对成绩表按名字分组后,显示每一类名字的Django的分数总和>150的
-- 类名字和django总分
--INSERT INTO ExamResult VALUES (12,"alex",90,90,90);
select name,sum(Django) from ExamResult group by name
having sum(Django)>150;
/*
having 和 where两者都可以对查询结果进行进一步的过滤,差别有:
<1>where语句只能用在分组之前的筛选,having可以用在分组之后的筛选;
<2>使用where语句的地方都可以用having进行替换
<3>having中可以用聚合函数,where中就不行。
*/
-- 练习:对成绩表按名字分组后,显示除了yuan这一组以外的每一类名字的Django
-- 的分数总和>150的类名字和django总分
select name,sum(Django) from ExamResult
WHERE name!="yuan"
group by name
having sum(Django)>130;
-- GROUP_CONCAT() 函数
SELECT id,GROUP_CONCAT(name),GROUP_CONCAT(JS) from ExamResult GROUP BY id;
--<1> 统计表中所有记录
-- COUNT(列名):统计行的个数
-- 统计一个班级共有多少学生?先查出所有的学生,再用count包上
select count(*) from ExamResult;
-- 统计JS成绩大于70的学生有多少个?
select count(JS) from ExamResult where JS>70;
-- 统计总分大于280的人数有多少?
select count(name) from ExamResult
where (ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0))>280;
-- 注意:count(*)统计所有行; count(字段)不统计null值.
-- SUM(列名):统计满足条件的行的内容和
-- 统计一个班级JS总成绩?先查出所有的JS成绩,再用sum包上
select JS as JS总成绩 from ExamResult;
select sum(JS) as JS总成绩 from ExamResult;
-- 统计一个班级各科分别的总成绩
select sum(JS) as JS总成绩,
sum(Django) as Django总成绩,
sum(Database) as Database总成绩 from ExamResult;
-- 统计一个班级各科的成绩总和
select sum(ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0))
as 总成绩 from ExamResult;
-- 统计一个班级JS成绩平均分
select sum(JS)/count(*) from ExamResult ;
-- 注意:sum仅对数值起作用,否则会报错。
-- AVG(列名):
-- 求一个班级JS平均分?先查出所有的JS分,然后用avg包上。
select avg(ifnull(JS,0)) from ExamResult;
-- 求一个班级总分平均分
select avg((ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0)))
from ExamResult ;
-- Max、Min
-- 求班级最高分和最低分(数值范围在统计中特别有用)
select Max((ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0)))
最高分 from ExamResult;
select Min((ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0)))
最低分 from ExamResult;
-- 注意:null 和所有的数计算都是null,所以需要用ifnull将null转换为0!
-- -----ifnull(JS,0)
-- with rollup的使用
--<2> 统计分组后的组记录
– (7) 重点:Select from where group by having order by – Mysql在执行sql语句时的执行顺序:from where select group by having order by – 分析: select JS as JS成绩 from ExamResult where JS成绩 >70; ---- 不成功 select JS as JS成绩 from ExamResult having JS成绩 >90; — 成功 select JS as JS成绩 from ExamResult group by JS成绩 having JS成绩 >80; ----成功 select JS as JS成绩 from ExamResult order by JS成绩;----成功 select * from ExamResult as 成绩 where 成绩.JS>85; ---- 成功
– (8) limit SELECT * from ExamResult limit 1; SELECT * from ExamResult limit 1,5;
外键约束
创建外键
--- 每一个班主任会对应多个学生 , 而每个学生只能对应一个班主任
— 主表
CREATE TABLE ClassCharger(
id TINYINT PRIMARY KEY auto_increment,
name VARCHAR (20),
age INT ,
is_marriged boolean -- show create table ClassCharger: tinyint(1)
);
INSERT INTO ClassCharger (name,age,is_marriged) VALUES (“冰冰”,12,0), (“丹丹”,14,0), (“歪歪”,22,0), (“姗姗”,20,0), (“小雨”,21,0);
— 子表
CREATE TABLE Student(
id INT PRIMARY KEY auto_increment,
name VARCHAR (20),
charger_id TINYINT, --切记:作为外键一定要和关联主键的数据类型保持一致
-- [ADD CONSTRAINT charger_fk_stu]FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
-- mysql不支持全外连接 full JOIN
-- mysql可以使用此种方式间接实现全外连接
select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id
UNION
select * from employee LEFT JOIN department on employee.dept_id = department.dept_id;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
| 1 | A | 19 | 200 | 200 | 人事部 |
| 2 | B | 26 | 201 | 201 | 技术部 |
| 3 | C | 30 | 201 | 201 | 技术部 |
| 4 | D | 24 | 202 | 202 | 销售部 |
| 5 | E | 20 | 200 | 200 | 人事部 |
| NULL | NULL | NULL | NULL | 203 | 财政部 |
| 6 | F | 38 | 204 | NULL | NULL |
+--------+----------+------+---------+---------+-----------+
-- 注意 union与union all的区别:union会去掉相同的纪录</code></pre>
多表查询之复合条件链接查询
-- 查询员工年龄大于等于25岁的部门
SELECT DISTINCT department.dept_name
FROM employee,department
WHERE employee.dept_id = department.dept_id
AND age>25;
–以内连接的方式查询employee和department表,并且以age字段的升序方式显示
select employee.emp_id,employee.emp_name,employee.age,department.dept_name
from employee,department
where employee.dept_id = department.dept_id
order by age asc;</code></pre>
select * from employee
WHERE EXISTS
(SELECT dept_name from department where dept_id=203);
--department表中存在dept_id=203,Ture
select * from employee<br> WHERE EXISTS
(SELECT dept_name from department where dept_id=205);
-- Empty set (0.00 sec)
ps: create table t1(select * from t2);</code></pre>