Mysql operations for MAC

• Mysql is a database manage system(DBMS)which is open source code
• Three part of SQL
  • DDL(data define language)
  • DML(data manage language)
  • DCL(data control language)

DDL(some operations to table,schema,domain,index and view)

Here are some command line of the mysql:

• CREATE(will make sure the name of table and column, the type of data and a construction)
  • CREATE TABLE CD( CD_ID INTEGER NOT NULL,CD_TITLE CHARCTER(30);)
• ALTER(which can realize the change function)
  • ALTER TABLE CD ADD NO_DISCS INTEGER;
• DROP(delete this table/or some data from the database)
  • DROP TABLE CD;

1. line: create database NAME;
   
2. line:show databases;
   
3. line:drop(=delete) database NAME;
   line: and reshow the list of database->show databases;
   
4. enter a specify database:line->use NAME;
   
5. view the database currently in use:line-> select database();
   If there no database is linked, the statu will be NULL.
   Here: the statu is successful to link.
   

The line of table

To create a new database t for table line

1. Line: create data table

CREATE TABLE the_name_of_table(
the_name_of_column1 the_type_of_data [limitation],
the_name_of_column2 the_type_of_data [limitation],
the_name_of_column3 the_type_of_data [limitation]
);

create table:student, and the column: name, age,sex.
And view the information of the column.


2. Alter table:line-> alter table Name add new_column_name type

3. change the column->line: alter table Name change previous_name new_name new_type

4. delete the column->line: alter table sName drop column_name

5. change the name of table->line :alter table previous_name rename new_name;

6. view the detail of created table：line-> show create table Name;
7. change the character (if it’s already utf8,not change it)
Line->alter table Name charecter set 编码方式;

DML(be used to manage the data part of database)

• SELECT
  -SELECT * FROM CD WHERE CD_COMPANY='CBS MUSIC';//return all rows of this circumstance
• UPDATE
• DELETE
• INSERT(用户能够直接向表中添加数据）

1. insert operation

• insert into table_name/column_name values;
• eg:insert into user(username,userage,usersex,birthday)values(‘xx’,18,‘a’,‘2000-1-1’);
  
• some tips:
  • 隔断用逗号
  • 列名和值要一一对应
  • 非数值的列值两侧要加单引号
  • 添加数据的时候可以将列名省略，但必须是当给所有列添加数据
  • 如果插入空值用NULL
  • 插入日期也需要用引号
  • 参数值不要超出列定义的长度
• insert more than one row
• eg:insert into user(username,userage,usersex,birthday)values(‘xx’,18,‘a’,‘2000-1-1’),(‘yy’,19,‘F’),(‘zz’,20,‘M’);
  

2. update operations

• change a column’s value together
• line->:update Name_of _table set column1=value1,column2=value2,column3=value3…where columnx=value(if change everything will not need this where syntax)
  “where be used in a specific situation”
  
  
• Change multiple columns in a row
  ->line: update user set username=‘usr’,userage=‘20’ where sex=‘M’;

3. supply of Operators

• Arithmetic operators：+ - * /
• Assignment operators：from right to left :=
• logic operators: and,or,not
• relationship operators :>,<,>=,<=,=,<>(not equal)
• tips
  • line: xxx=’ ’ is same with xxx is null;
  • line: age!=10 or age!=20;(right) age!=10 or 20;(wrong)
    

4. delete operations

• delete all or delete a part
• line->: delete from table_name where column=‘value’;
• delete all information of table(the information lost but the table still at here)
  4.1 the second way of delete:truncate table table_name;
  与delete的区别： DELETE 删除表中的数据，表结构还在;删除后的数据可以找回
• TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。
• 删除的数据不能找回。执行速度比DELETE快。

DCL(data control language:Is used to change or set the authority of User of a database)

1. Create a user(here are three ways for this operation)

• the line-> create user ‘THR_NAMA_OF_USER’@‘specifyip’ identified by ‘PASSWORD’;

• eg: a user with name:‘alice’ & specifyip:‘localhost’ & password:‘abc’

• So the line is : CREATE USER ‘alice’@‘localhost’ IDENTIFIED BY ‘123’;
  

• The line->:create user ‘the_name_of_user’@‘the_ip_of_clientside’ IDENTIFIED by ‘password’;
  

• create user 用户名@‘% ’ identified by 密码 任意IP均可登陆 （通常用于给团队其他成员授权）

• eg：create user test7@‘%’ IDENTIFIED by ‘test7’

2. Grant to users(授权，authorization）

First,query the permission of the specified user->The line: show grants for ‘The_name_of_specified_username’@localhost.

一、Grant the specified users the specified permissions to all tables in the specified database.

The permission:(select, insert, update, delete ,create)
grant permission1,permission2,…,perimissionn on The_name_of_database.* to ‘The_user_name’ @IP; （The database name . means all the tables in the database）
Eg.

二. grant all peimissions of the all database’ all table for the specified users.
The line->:

三：revoke the permission of users
The line-> :revoke perimission1,permission2,……,permission n on The_name_of_database.* from The_name_of_user@IP;

四：drop user
The line->:drop user ‘The_name_of_user’@IP

quit the mysql and test with Alice this username to log In.
here: success delete the Alice.

DQL(the data query language)

• When the database run the DQL will not change the information of data, it will send the set of outcome to the client-side by the database.
• Finishing the query, the outcome will return a virtual table.

1. SELECT
   The line-> Select the_name_of_column from The_name_of_table
   Eg. Select *from table_name;

1:create a new table stu (for some operations)

2:insert some information into the table (and select *from stu;)

3: select information according to the column name
The line-> select column1,column2 from table_name;

4:select according by conditional(using "where"keyword to limit the range ,and using the logic operator)
Some examples:




summary:

• 查询所有列
• 查询指定列
  • select sid,sname from stu;
• 条件查询
  • selece *from stu where age>20;
• 范围查询
  • select *from stu where sid=‘s_1001’ or sid=‘s_1002’ or sid=‘s_1003’;
  • select *from stu where sid in(‘s_1001’,‘s_1002’,‘s_1003’);
  • select *from stu where sid not in(‘s_1001’,‘s_1002’,‘s_1003’);
    -NULL
  • select *from stu where age is NULL;
  • select *from stu where age not is NULL;

2. 模糊查询
   The line-> column_name like ‘表达式’ // 表达式必须是字符串
   通配符:
   (下划线：‘-’): 任意一个字符
   %：任意0~n个字符,‘张%’
   eg：“白_” 代表白某，而 “白%” 则代表白某或者白某某（任意长度）

• 查名字由六个字母组成（用六个下划线表示‘______')
  
• 查名字最后一位是n的同学（‘_____n’:五个下划线+n）
  
• 查名字是z开头的同学（‘z%’)
  
• 查名字第二个字母是i的同学（'_u% ')
  
• 查名字里带a的同学（‘%a%’)
  

3. Field control queries（字段控制查询）

• Duplicate records are removed, and the data in the columns is the same in two or more rows
  Eg：I want to know how many genders at here.Maybe the column will have same information, like this.
  so I want to delete the same information just keep every data only once(no repeat)(去除重复内容)

So, we can use the keyword “DISTINCT” to delete the same information.
the line->select the distinct column_name from table_name;

• Select the sum of columns (the columns must can be added)
  The line-> select the_name_of_column1+the_name_of_column2 from the_name_of_table
  Eg. select sal+money from stu;
  First, create an instance of this line:
  
  
  如果有数据是NULL的话，遇到这种情况需要把null转为数字0
  所以用到：IFNULL函数
  The line: select the_name_of_column+ifnull( the column which need to be change,0) from table_name;
  Eg: select usersalary+ifnull(money,0) from user;
  
• Give a specific column with nickname
  Just like the last example of get sum of two columns, the column name is to long too convinient to use,so you can give this column a new nickname for easily to use it.

The line: select the_column as new_nickname_of_thiscolumn from table;
eg: select usersalary+ifnull(money,0) as/ mysql from user;(as or a vocumn)

Sort of table

The line: order by column_name asc/desc
(asc:ascending order)(desc:descending order)(default：asc）

• Select all records sorting by the asc ordering:
  
  
• Multi-columns sort(order by c1 desc, c2 desc: if c1’s information is same then according to the c2’s value to sort)
  查询所有的人员，按照薪水排序，如果薪水相同时，按照money来降序排序。

Aggregation function（It is used by counting for the portrait ）

• count examples:
  

1. 查询表中的所有行数
2. 查询表中有money的所有行数
3. 查询表中money大于200和小于200的所有行数
4. 查询表中money+salary之和大于2500的所有行数

• Sum and Avg and max or min
  The line：select sum(column_name) from table_name;
  select avg(column_name)from table_name;
  select max(column_name),min(column_name) from table_name;
  
  

分组查询和having子句和limit的运用