mysql学习

mysql命令：
？ 查询当前的一些命令
mysql> ?
For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/
List of all MySQL commands:
Note that all text commands must be first on line and end with ‘;‘
?         (\?) Synonym for `help‘.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don‘t write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don‘t show warnings after every statement.
For server side help, type ‘help contents‘
clear     (\c) 终止的意思
如：
mysql> show databasedsfdsaf \c
delimiter (\d) 设置语句结束的分隔符
如：
mysql> \d //
mysql> show databases;
    -> //
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)
mysql> show databases //
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)
ego       (\G)  不管你定义的任何的分隔符一定要执行最后结果，并竖排
如：
mysql> show databases \G
*************************** 1. row ***************************
Database: information_schema
*************************** 2. row ***************************
Database: mysql
*************************** 3. row ***************************
Database: test
3 rows in set (0.00 sec)
go        (\g) Send command to mysql server
source    (\.) 导入文件 等同于mysql -uroot -pdtsdts >mysql.sql
如：
mysql> source linux.sql
system    (\!)  不用退出mysql也可以查看/etc下面的文件
如：
mysql> \! ls /etc
warnings  (\W) 显示警告信息
nowarning (\w) 关闭警告信息
mysql也支持命令自动补齐
方法一：
编辑/etc/my.cnf
添加以下内容
[mysql]
no-auto-rehash
方法二：
使用命令：mysql -A  
 mysqladmin -uroot -hlocalhost -predhat ping 检测mysql是否存活   
   
 ysqladmin -uroot -plinuxsed processlist
复制一个表内容但是结构会丢失：
create tables linuxawk select *from user;
复制一个表结构，不会复制内容
create table tbt5 like tb4;
查看一个表的状态：
show table status like "user"\G;
修改默认存储引擎：
default_storage_engine=Innodb
create语法：
create table tb3(ID smallint unsigned not null auto_increment primary key,Name char(30) not null,AGE tinyint unsigned,SALARY float(10,2)not null );
mysql> desc tb3;
+--------+----------------------+------+-----+---------+----------------+
| Field  | Type                 | Null | Key | Default | Extra          |
+--------+----------------------+------+-----+---------+----------------+
| ID     | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| Name   | char(30)             | NO   |     | NULL    |                |
| AGE    | tinyint(3) unsigned  | YES  |     | NULL    |                |
| SALARY | float(10,2)          | NO   |     | NULL    |                |
+--------+----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into tb3(name,age,salary)values("Jerry",23,23333.50);
Query OK, 1 row affected (0.06 sec)
mysql> select *from tb3
    -> ;
+----+-------+------+----------+
| ID | Name  | AGE  | SALARY   |
+----+-------+------+----------+
|  1 | Jerry |   23 | 23333.50 |
+----+-------+------+----------+
1 row in set (0.00 sec)
mysql> create table tb4(ID smallint unsigned not null auto_increment primary key ,Name char(50)not null,GENDER char(1)not null default ‘M‘,BDATE date not null)engine=myisam charset=utf8;
Query OK, 0 rows affected (0.11 sec)
数据库定义语言：DDL
create drop alter
查看当前mysql用户的权限
mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*CEFE9CF698C1B1FA9D0B010DCD146963187D8002‘ WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
查看指定用户的权限
mysql> show grants for root@‘localhost‘;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*CEFE9CF698C1B1FA9D0B010DCD146963187D8002‘ WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
查看mysql所有用户的权限
mysql> SELECT DISTINCT CONCAT(‘User: ‘‘‘,user,‘‘‘@‘‘‘,host,‘‘‘;‘) AS query FROM mysql.user;
+---------------------------------------+
| query                                 |
+---------------------------------------+
| User: ‘root‘@‘127.0.0.1‘;             |
| User: ‘‘@‘localhost‘;                 |
| User: ‘root‘@‘localhost‘;             |
| User: ‘‘@‘localhost.localdomain‘;     |
| User: ‘root‘@‘localhost.localdomain‘; |
+---------------------------------------+
5 rows in set (0.00 sec)
原表
mysql> desc tb4;
+---------+----------------------+------+-----+---------+----------------+
| Field   | Type                 | Null | Key | Default | Extra          |
+---------+----------------------+------+-----+---------+----------------+
| ID      | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| Name    | char(30)             | NO   |     | NULL    |                |
| AGE     | tinyint(3) unsigned  | YES  |     | NULL    |                |
| SALARY  | float(10,2)          | NO   |     | NULL    |                |
| Subject | char(10)             | NO   |     | NULL    |                |
+---------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
alter添加一下字段
mysql> alter table tb4 add linuxsed char(10) not null;
Query OK, 1 row affected (0.10 sec)
Records: 1  Duplicates: 0  Warnings: 0
alter删除一个字段
mysql> alter table tb4 drop linuxsed;
Query OK, 1 row affected (0.09 sec)
Records: 1  Duplicates: 0  Warnings: 0
alter修改字段类型
mysql> alter table tb4 modify age smallint unsigned not null;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0
alter修改字段名称
mysql> alter table tb4 change age old smallint unsigned not null;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0
alter修改表的默认存储引擎
mysql> alter table tb4 engine=‘myisam‘;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0
alter重命名表
mysql> alter table tb4 rename  to linuxsed;
Query OK, 0 rows affected (0.00 sec)
DML
truncate 和 delete的效率问题：
如果想要删除表的所有数据，truncate语句要比 delete 语句快。因为 truncate 删除了表，然后根据表结构重新建立它，而 delete 删除的是记录，并没有尝试去修改表。这也是为什么当向一个使用 delete 清空的表插入数据时，MySQL 会记住前面产生的AUTOINCREMENT序列，并且继续利用它对AUTOINCREMENT字段编号。而truncate删除表后，表是从1开始为autoincrement字段编号。
不过truncate命令快规快，却不像delete命令那样对事务处理是安全的。因此，如果我们想要执行truncate删除的表正在进行事务处理，这个命令就会产生退出并产生错误信息
alter添加指定字段后一行
 alter table  stu add StuID tinyint not null after ID;
   
 create table classes (ClassID smallint unsigned not null auto_increment primary key ,Class varchar(50) not null,NumofStu tinyint unsigned,Tutor smallint unsigned,CourseID1 smallint unsigned,CourseID2 smallint unsigned,CourseID3 smallint unsigned);
   
   
 insert into classes (Class,NumofStu,Tutor,CourseID1,CourseID2,CourseID3) values (‘Class01‘,36,2,2,4,5),(‘Class02‘,41,1,1,3,4),(‘Class03‘,85,4,5,3,2);

本文出自 “linuxsed” 博客，谢绝转载！

mysql学习,古老的榕树,5-wow.com