mysql基本操作

创建库：create database test1 charset utf8;

查看库：show databases;
查看创库语句：show create database test1;
选择库：use test1
修改库：alter database test1 charset utf8mb4;
删除库：drop database test1;

创建表：create table stu (id int,name varchar(10));
创建相同表结构的表：create table stu1 like stu;
创建一个相同的备用表：create table stu2 as select * from stu;
查看表：show tables;
查看创表语句：show create table stu;
查看表结构：desc stu;
修改表名：alter table stu rename to student;
删除表：drop table stu;
删除大表：truncate table stu;(对于很大的表用drop删慢，可以先用truncate再用drop)

alter修改表：
1.在表最后加一列：alter table student add addr varchar(20);
2.在表头部加一列：alter table student add stu_id int first;
3.在name列后加一列：alter table student add qq int after name;
4.在age后加tel_num,在最后一行加email：alter table student add tel_num int after age,add email varchar(20);
5.删除某一列：alter table student drop id;
6.修改列名字：alter table student change name stu_name varchar(20);
7.修改列数据类型：alter table student modify gender varchar(20);

insert向表中插入数据：
1.插入单行：insert [into] student values(1,‘beizi‘,111,20,110,‘male‘,‘bj‘,‘123@qq.com‘);
2.指定列进行插入：insert into student(stu_id,stu_name,qq) values(2,‘li4‘,456);
3.多行插入：insert into student values(1,‘zhang3‘,123,20,110,‘male‘,‘bj‘,‘123@qq.com‘),(5,‘zz‘,12322,202,1102,‘female‘,‘bj‘,‘12322@qq.com‘);
4.一个表向另一个表插入：insert into stu0 select * from student;（表结构要一样）

update修改表数据：
修改一行：update student set stu_name=‘zhangsan‘ where stu_id=1;

delete删除表数据：
delete from student where stu_name=‘zhangsan‘;

注：delete删除比较危险，可以用标识符配合update进行伪删除：
alter table stu0 add state int default 1;
update stu0 set state=0 where stu_name=‘zhangsan‘;
select * from stu0 where state=1;

select查询语句详解下次继续

MySQL提供的权限:
INSERT,SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
授权权限：grant SELECT,INSERT on test.* to test@‘192.168.1.%‘ identified by ‘123456‘;
收回权限：revoke SELECT,INSERT on test.* from test@‘192.168.1.%‘;
查看权限：show grants for test@‘192.168.1.%‘;
提醒：
　　如果，库级别和表级别都设置了权限，那么对表操作时，权限是叠加。
查看mysql数据库user表里一些信息：
　　select user,host,password from mysql.user;
注：5.7密码字段不再是password了，被替换为了authentication_string
5.7数据库:
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+-----------+-------------------------------------------+
若是数据库密码忘了，想改数据库密码，在5.6里操作是：
a)关闭服务：service mysqld stop
b)停止授权启动：mysqld_safe --skip-grant-table --skip-networking & //--skip-grant-table跳过授权，--skip-grant-table禁止远程连接
c)更改密码：update mysql.user set password=PASSWORD(‘123‘) where user=‘root‘ and host=‘localhost‘;
　　（在5.7里将password替换为authentication_string）
d)重启mysql服务