mysql命令

导入 sql 表 > SOURCE /home/username/sample.sql
显示表结构 > DESCRIBE/DESC/EXPLAIN sampleTable;
使用数据库 > USE databaseName;
删除数据库 > DROP databaseName;
创建表 > CREATE TABLE sampleTable(id integer primary key auto_increment,name varchar(255));
重命名表 > RENAME TABLE oldName TO newName;
删除表 > DROP TABLE sampleTable;
给表增加列 > ALTER TABLE sampleTable ADD COLUMN phone integer (AFTER/BEFORE name);
修改表的列 > ALTER TABEL sampleTale CHANGE COLUMN phone phoneNumber varchar(255)/integer;
删除表的列 > ALTER TABLE sampleTable DROP COLUMN address;
插入表的列的值 > INSERT INTO sampleTable COLUMN (name,city) VALUE ("Thomas","Bristol");
插入表的行的更多值 > INSERT INTO sampleTabel COLUMN (name) VALUE ("Simon"),("Jackie"),("Thomas");
查询并打印全表 > SELECT * FROM sampleTable;
查询指定的列 > SELECT id,naem FROM sampleTable;
查询指定列并去除重复 > SELECT DISTINCT city FROM sampleTable;
利用 where 有条件打印 > SElECT * FROM sampleTable WHERE name = "Matt" OR city = "NYC";
利用 where 有条件打印 > SElECT * FROM sampleTable WHERE id <= 2 AND city = "Birmingham";
排列查询的数据 > SELECT * FROM sampleTable ORDER BY id DESC/ASC;
统计行的数据 > SELECT COUNT(*/name) FROM sampleTable;
模糊查询 > SELECT * FROM sampleTable WHERE city LIKE "_L%";
# %匹配任意多个字符, _匹配单个字符
查询指定多个列 > SELECT * FROM sampleTable WHERE city/id IN ("London","Leeds","Paris")/(1,4,3);
查询区间内的列 > SELECT * FROM sampleTable WHERE id (NOT) BETWEEN 1 AND 3;
更新行的值 > UPDATE sampleTable SET name = "Rod" (,city = "Manchester) WHERE id = 3;
删除指定数据 > DELETE FROM sampleTable WHERE id =5;
引用两个表通过相同点 > SELECT Persons.LastName,Persons.FirstName,Order.OrderNo From Persons,Orders WHERE Person,Id_P = Orders.Id_P;
引用两个表(交集) > SELECT Persons.LastName,Persons.Firstname,Order.OrderNo From Persons INNER JOIN Orders ON Persons.Id_P = Order.Id_P ORDER BY Persons_LastName;
引用两个表(左表交集） > SELECT Persons.LastName,Person.FirstName,Orders.OrderNo From Persons LEFT JOIN Orders ON Person.Id_P = Order.Id_p ORDER BY Person.LastName;
引用两个表(右表交集） > SELECT Persons.LastName,Person.FirstName,Orders.OrderNo From Persons RIGHT JOIN Orders ON Person.Id_P = Order.Id_p ORDER BY Person.LastName;
引用两个表通过相同点(并集） > SELECT Persons.LastName,Person.FirstName,Orders.OrderNo From Persons FULL JOIN Orders ON Person.Id_P = Order.Id_p ORDER BY Person.LastName;
统计并打印两个表间的数据 > SELECT name,COUNT(product) FROM customers INNER JOIN orders ON customers.id = order.customersID WHERE customers.id = 2;
分组 > SELECT name,COUNT(product) FROM customers INNER JOIN orders ON customers.id = orders.customerID GROUP BY customers.name;
查询打印出一个新列 > SELECT name,COUNT(product) AS Total_orders FROM customers INNER JOIN orders ON customers.id = orders.customerID GROUP BY customers.name;
给 AS 条件 通过 HAVING > SELECT name,COUNT(product) AS Total_orders FROM customers INNER JOIN orders ON customers.id = orders.customerID GROUP BY customers.name HAVING COUNT(product) >=2;
建立/删除索引 > CREATE/DROP INDEX idx_city ON customers(city);
显示 MySQL 用户 > SELECT user FROM mysql.user;
有条件创建新用户 > GRANT SELECT ON shop.* TO 'newUser'@'localhost' IDENTIFIED BY 'pass';
复制表 > CREATE TABLE (IF NOT EXISTS) newTable SELECT * FROM oldTable;
复制表结构 > CREATE TABLE (IF NOT EXISTS) newTable LIKE oldTable;