显示所有数据库 show databases;
创建数据库 create dababase stu;
删除数据库drop database stu;
切换到数据库use stu;
取得当前的数据库,带()表示函数:select database();
创建表(要切换到数据库中)
mysql> create table stu_info(
-> stu_id int(8),
-> stu_name varchar(48),
-> stu_sex char(2),
-> stu_age int(8));
查看表的结构
mysql> desc stu_info;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| stu_id | int(8) | YES | | NULL | |
| stu_name | varchar(48) | YES | | NULL | |
| stu_sex | char(2) | YES | | NULL | |
| stu_age | int(8) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
删除表
drop table stu_info;
查看数据库中所有的表show tables;
主键 primary key
增加字段 alert table stu_info add column stu_card varchar(20);
删除字段alter table stu_info drop column stu_card ;
修改字段 alter table stu_info modify column stu_id int(20);
delete from stu_info where []
insert into stu_info() values ()
select * from stu_info where []
mysql 分业技术 选择从第1条开始的3条数据
select * from stu_info limit 0,3 ;
选择从第1条开始的3条数据
select * from stu_info limit 5,3 ;
直接从文件中读取sql语句然后在mysql中执行
/. d://che//mysql1//mysql.sql
例如
mysql.sql文件中有
use student;
select * from stu_info;
执行 /. d://che//mysql1//mysql.sql后结果为
Database changed
+--------+----------+---------+---------+----------------+--------------+
| stu_id | stu_name | stu_sex | stu_age | stu_adr | stu_birthday |
+--------+----------+---------+---------+----------------+--------------+
| 1 | 车燕兵 | 男 | 25 | cheyanbing.com | 2009-01-02 |
| 2 | che | man | 23 | gfdfgdsf | 2008-12-23 |
| 3 | che | man | 23 | gfdfgdsf | 2008-12-23 |
| 4 | che | man | 23 | gfdfgdsf | 2008-12-23 |
| 5 | che | man | 23 | gfdfgdsf | 2008-12-23 |
| 6 | che | man | 23 | gfdfgdsf | 2008-12-23 |
| 7 | che | man | 23 | gfdfgdsf | 2008-12-23 |
| 8 | che | man | 23 | gfdfgdsf | 2008-12-23 |
| 9 | che | man | 23 | gfdfgdsf | 2008-12-23 |
| 10 | che | man | 23 | gfdfgdsf | 2008-12-23 |
| 11 | che | man | 23 | gfdfgdsf | 2008-12-23 |
| 12 | cyb | man | 23 | jiangxiganzhou | 2009-02-21 |
+--------+----------+---------+---------+----------------+--------------+
12 rows in set (0.00 sec)
排序方式
asc 按升序 ascend升序
desc 按降序 descend降序
select * from stu_info order by stu_id asc, stu_age desc;//先按学号升序,再按年龄降序排
常用函数
计算记录的行数 count()
select count(*) from stu_info;//求所有记录的条数,可以加条件表达式
select count(stu_sex) from stu_info;//注意当stu_sex为空的时候,不会被计算进去
select count(*),count(stu_sex) from stu_info;
+----------+----------------+
| count(*) | count(stu_sex) |
+----------+----------------+
| 7 | 1 |
+----------+----------------+
对字段求和 sum()
select sum(stu_age) from stu_info;//字段只能是数字型,不能为其他类型
+--------------+
| sum(stu_age) |
+--------------+
| 175 |
+--------------+
求平均数函数 avg()
select avg(stu_id) from stu_info;//空字段不参与求平均
+-------------+
| avg(stu_id) |
+-------------+
| 4.0000 |
+-------------+
日期函数
select now();
+-------------+
| avg(stu_id) |
+-------------+
| 4.0000 |
+-------------+
将日期转换为天数 to_days()
select to_days(stu_birthday) from stu_info;
+-----------------------+
| to_days(stu_birthday) |
+-----------------------+
| 733774 |
+-----------------------+
数学函数
abs()
mod()
round()
字符串函数
length(str)
left(str,len)
right(str,len)
substring(str,pos,len)//下标从1开始
ascii(str)
concat(str1,str2,...)
创建外键关联
create table employee(
id int primary key,
dep_id int,
name varchar(20),
constraint fk_emp_dept_id foreign key(dep_id) references department(id));
//constraint约束
删除外键关联关系
alter table employee drop foreign key fk_emp_dept_id;
再加drop index fk_emp_dept_id on employee;
外键所在的表叫父表,该表叫子表,
以后操作的时候删除父表id要同时删除子表的记录,否则麻烦就大了 。
修改外键
alter table employee add constraint fk_emp_dept_id foreign key(dep_id) references department(id);
显示用于创建给定表的CREATE TABLE语句
show create table employee;
删除主键
alter table stu_info drop primary key ;
创建主键
alter table stu_info modify stu_id int primary key;
建立索引
create index emp_index on employee(name);
删除索引
drop index emp_index on employee;
多表联合查询
select employee.id,employee.dep_id,department.id,department.name from employee,department where employee.dep_id=department.id;
数据库备份
//注意一定要在dos下操作,不能在mysql客户端操作
备份一个数据库
mysqldump -u root -p --opt student>d:/che/test.sql
opt表示优化数据库
备份所有数据库
mysqldump -u root -p --all-database>d:/che/all.sql
备份数据库中的单个表
mysqldump -u root -p --opt student stu_info>d:/che/stu_info.sql
备份数据库中的多个表
mysqldump -u root -p --opt student stu_info employee>d:/double.sql
数据库恢复
恢复一个数据库
mysql -u root -p student <d:/che/test.sql
恢复所有数据库
mysql -u root -p <d:/che/all.sql
恢复数据库中的单个表
mysql -u root -p student<d:/che/stu_info.sql
恢复数据库中的单个表
mysql -u root -p student<d:/che/double.sql
授予权限//如果没有该用户则创建该用户
grant [权限] on [数据库名].[表名] to ['用户名']@['web服务器的ip地址'] identified by ['密码']
with grant option;
所有权限
grant all privileges on student.* to cyb@cheyanbing.com identified by 'cyb';
赋予student数据库所有权限
赋予部分权限
grant select,alter on student.* to cyb1@cheyanbing.com identified by 'cyb';
回收权限
revoke select on student.* from cyb@cheyanbing.com
删除用户
delete from user where User='cyb'
记得使用commit;
flush privileges;
修改密码
update user set password=PASSWORD('cheyanbing')where user='cyb1';
记得使用commit;
flush privileges;
实现将查询的结果记录保存到文件中命令
select list from table into outfile '文件名';
例如:
select * from tbl_corporation into outfile 'd:/test.txt';