MySQL基础语句总结
MySQL中的创建库、表以及查询语句对我们以后很好的应用数据库是很大有帮助的,文中是对这些基础语句的总结,希望会对大家有些帮助
1、创建与删除数据库
-
创建数据库
-
mysql> createdatabase testdb;
-
mysql> createdatabase if not exists testdb;
-
mysql> createschema if not exists student characterset'gbk'collate'gbk_chinese_ci';
-
删除数据库
-
mysql> dropdatabase testdb;
2、创建与删除表
- CREATETABLE [if not exists] tb_name(col_name,col_definstion,constraint)
- 创建表
- mysql> createtable tb (id int unsigned notnull auto_increment primarykey,Namechar(20)
- notnull,Age tinyint notnull);
- mysql> createtable tb (id int unsigned notnull auto_increment,Namechar(20) notnull,Age
- tinyint notnull,primarykey(id));
- mysql> createdatabase mydb;
- mysql> use mydb;
- mysql> createtable students(namechar(20) notnull,age tinyint unsigned,gender char(1)
- notnull);
- mysql> createtable courses(ID tinyint unsigned notnull auto_increment primarykey,Couse
- varchar(50) notnull);
- mysql> createtable courses(namechar(20) notnull,age tinyint unsigned,gender char(1)
- notnull);
- mysql> createtable testcourses select * from courses where CID <=2;
- 以其它表为模板,创建一个新表,字段的属性还会存在
- mysql> createtable test like courses;
- 删除表:DROPTABLE tb_name;
- mysql> droptable testcourses;
3、修改表
- ALTERTABLE tb_name;
- mysql>altertable students change course Course varchar(100) aftername;
- mysql>altertable students add course varchar(100);
- 向表中插入数据
- insertinto tb_name (col,col2,....) values (val1,val2,....);
- insertinto tutors (Tname,Gender,Age) values ('jerry','M',24); -----批量插入方式
- insertinto tutors set Tname='Tom',Genser='F',Age=30; -----只能实现单个字段插入
- insertinto tutors (Tname,Gender,Age) selectName,Genser,Age from students where Age >=20
- select * from tutors orderby TID desc limit 1; -----查看降序的第一行
- select last_insert_ID(); -----查询插入的最后一个序列号
- 更改数据库
- UPDATE tb_name SETcolumn=value where
- mysql>update students set Course='wg'whereName='j'; -----更改j的课程为wg
- 删除表中的某一字段
- DELETEFROM students WHERE Course='';
- mysql>deletefrom students where Course='wg';
4、创建用户
-
CREATEUSER'USERNAME'@'HOST' IDENTIFIED BY'PASSWORD';
-
mysql> createuser'jerry'@'%' identified by'jerry'; ------创建用户
-
修改用户密码的方法
-
1) mysql> SETPASSWORDFOR'USERNAME'@'HOST'=PASSWORD('NEW_PASSWORD');
-
2) mysqladmin -uUSERNAME -hHOST -p password'password' ------不进入mysql修改密码
-
mysqladmin -uroot -h127.0.0.1 -p passwd'123456'
-
3) mysql> UPDATEuserSETPassword=PASSWORD('password') WHEREUSER='root'AND
-
Host='127.0.0.1';
-
UPDATEuserSETPassword=PASSWORD('123456') WHEREUSER='root'AND Host='127.0.0.1';
-
给用户授权
-
GRANT pri1,pri2,......ON DB_NAME.TB_NAME TO'USERNAME'@'HOST' [IDENTIFIED BY'password']
-
mysql> grantallprivilegeson test.* to'jerry'@'%'; -----给用户所有权限
-
REVOKE pri1,pri2,.....ON DB_NAME.TB_NAME FROM'USERNAME'@'HOST'
5、关于show命令
-
SHOW GRANTS FOR'USERNAME'@'HOST' -----查看用户的授权
-
mysql> show databases; -----查看数据库
-
mysql> desc students; -----查看一张表的结够
-
mysql> show grants for'jerry'@'%'; -----查看创建用户的信息
-
mysql> show characterset; -----查看当前服务器所支持的字符集
-
mysql> show collation; -----查看排序规则
-
mysql> show engines; -----查看数据库中的所有引擎
-
mysql> show table status like'user'; -----查看一张表的状态信息(横向显示的)
-
mysql> show table status like'user'\G: -----查看一张表的状态信息(纵向显示)
评论暂时关闭